SqlBase

class PlasmaCalcs.tools.sql_tools.SqlBase(location=None, *, assert_exists=None)

Bases: object

class which provides some basic SQL-related tools for a database connection.

location: None or str

None –> in-memory, equivalent to “:memory:”. Will not edit any files.

str –> connection string. Filepath for local sqlite connection.

Online database connection string starts with “postgresql://” or “postgres://”.

assert_exists: None, True, or False

True –> assert that implied location already exists as a file or online database.

False –> assert that the implied location does not yet exist.

None –> no assertion is made.

(Here, ‘:memory:’ never “already exists”, since it always means a new database.)

Methods

column_types(table)	returns the types of columns in this table in this database, as str-valued dict.
columns(table)	returns list of columns names in this table in this database.
connect()	return a connection to this database, creating it if it does not exist yet.
execute(*args, **kwargs)	shortcut for with self.connect() as con: return con.execute(*args, **kwargs)
get_tables(*[, _con])	return list of all table names in this database.
missing_columns(table, columns)	returns list of which of these columns do not yet exist in table in this database.
primary_key(table)	returns the single primary key column name in this table in this database,
primary_keys(table)	returns list of primary key column names in this table in this database.
sanitize_value(v)	return sanitized value v, so that it can be stored in a SQL database.
sanitize_values(d)	return copy of dict d but with values sanitized to number, None, or string,
text_columns(table)	returns the columns in this table whose types are text-based, e.g. TEXT, CHAR(32).
_location_display()	return self.location but without any sensitive info, suitable for displaying in repr.
_location_exists()	return whether self.location already exists,
_repr_contents()	list of contents for self.__repr__

Attributes

engine	sql engine implied by location string.
engine_backend	sql engine "backend" implied by engine string.
is_local	returns whether self corresponds to a local database connection.
sizes	dict of sizes (number of entries) of all tables in this database.
tables	list of all table names in this database.

_location_display()

return self.location but without any sensitive info, suitable for displaying in repr.

For in-memory or filepath locations, just use self.location directly.

For online database connection strings, just display engine and database name.

_location_exists()

return whether self.location already exists,

as a cached in-memory connection, a file, or an online database.

Caution: for online database, always returns True, i.e. assume it exists,

because this code is not able to create a new online database from scratch.

Subclass may want to implement self.schema_exists() which uses this method,

and tells whether the relevant schema has been initialized already.

_repr_contents()

list of contents for self.__repr__

column_types(table)

returns the types of columns in this table in this database, as str-valued dict.

columns(table)

returns list of columns names in this table in this database.

connect()

return a connection to this database, creating it if it does not exist yet.

(Cannot create online databases; those will just crash if connection fails.)

Can be used as a context manager to ensure it is closed properly:

with sqlbase.connect() as con:

results = con.execute(‘SELECT …’).fetchall()

Else, caller is responsible for closing the connection via con.close().

property engine

sql engine implied by location string. E.g. ‘memory’, ‘sqlite’, or ‘postgres’.

Might be more specific than just an sql dialect. See also: self.engine_backend.

property engine_backend

sql engine “backend” implied by engine string. Corresponds to an sql dialect.

E.g., ‘sqlite’ or ‘postgres’, but not ‘memory’ (‘memory’ engine <–> ‘sqlite’ backend).

execute(*args, **kwargs)

shortcut for with self.connect() as con: return con.execute(*args, **kwargs)

get_tables(*, _con=None)

return list of all table names in this database.

_con: None or open connection object

for efficiency, can provide connection object if already created.

property is_local

returns whether self corresponds to a local database connection.

Doesn’t require the connection to actually exist. Currently just checks engine,

e.g. ‘postgres’ –> is_local=False; ‘memory’ or ‘sqlite’ –> is_local=True.

memory_connection_cls

alias of Sqlite3InMemoryConnection

missing_columns(table, columns)

returns list of which of these columns do not yet exist in table in this database.

postgres_connection_cls

alias of PostgresConnection

primary_key(table)

returns the single primary key column name in this table in this database,

crashing if there is not exactly 1 primary key.

primary_keys(table)

returns list of primary key column names in this table in this database.

sanitize_value(v)

return sanitized value v, so that it can be stored in a SQL database.

Sanitized means number, None, or string.

sanitize_values(d)

return copy of dict d but with values sanitized to number, None, or string,

so that they can be stored in a SQL database.

property sizes

dict of sizes (number of entries) of all tables in this database.

property tables

list of all table names in this database.

text_columns(table)

returns the columns in this table whose types are text-based, e.g. TEXT, CHAR(32).