PostgresConnection

class PlasmaCalcs.tools.sql_tools.PostgresConnection(location)

Bases: object

class which wraps a database connection to an online PostgreSQL database.

Allows for sqlite3-like syntax, including executescript(), and using ‘?’ placeholders.

location: str

online database connection string, starting with “postgresql://” or “postgres://”.

Methods

__enter__()	enter "with" block; returns self (which behaves like psycopg connection)
__exit__(exc_type, exc_value, traceback)	exit "with" block; closes connection.
__getattr__(name)	pass through to self.con any attribute access not defined here.
execute(query[, params])	execute SQL query.
executescript(sql)	execute multiple SQL statements at once, separated by semicolons.
sql_to_postgres(sql, *[, dialect, as_list, ...])	transpile sql statement into postgres.

__enter__()

enter “with” block; returns self (which behaves like psycopg connection)

__exit__(exc_type, exc_value, traceback)

exit “with” block; closes connection.

__getattr__(name)

pass through to self.con any attribute access not defined here.

execute(query, params=None, **kw_execute)

execute SQL query. Can be written in sqlite-like syntax, e.g. using ? placeholders.

executescript(sql)

execute multiple SQL statements at once, separated by semicolons.

psycopg doesn’t have executescript(), so we need to implement it ourselves.

static sql_to_postgres(sql, *, dialect=UNSET, as_list=False, suppress_warnings=True)

transpile sql statement into postgres.

dialect: UNSET, None, or str

The dialect corresponding to the input SQL.

UNSET –> auto-detect; checks sqlite first, then postgres, then generic sql.

None –> use generic sql, i.e. sqlglot’s default dialect.

str –> e.g., “sqlite”, “postgres”. Can be any dialect string recognized by sqlglot.

as_list: bool

whether to return result as a list of sql statements.

False –> return as a single string, with statements separated by semicolons and newlines.

The last statement will not have a semicolon at the end.

True –> return a list of sql statements, none of which end with a semicolon.

suppress_warnings: bool or int

whether to suppress sqlglot warnings, e.g. about unsupported features of a dialect.