API

This part of the documentation covers all the interfaces of Mara Page. For parts where the package depends on external libraries, we document the most important right here and provide links to the canonical documentation.

DBs

mara_db.dbs.db(alias)

Returns a database configuration by alias

mara_db.dbs.connect(db: object, **kargs) → object
mara_db.dbs.connect(db: mara_db.dbs.PostgreSQLDB, **kargs) → psycopg2.extensions.cursor
mara_db.dbs.connect(db: mara_db.dbs.BigQueryDB, **kargs) → object
mara_db.dbs.connect(db: mara_db.dbs.MysqlDB, **kargs) → MySQLdb.cursors.Cursor
mara_db.dbs.connect(db: mara_db.dbs.SQLServerDB, **kargs) → pyodbc.Cursor
mara_db.dbs.connect(db: mara_db.dbs.SQLiteDB, **kargs) → sqlite3.Connection
mara_db.dbs.connect(db: mara_db.dbs.DatabricksDB, **kargs) → object

Creating a connection to the database object DB-API 2.0 (PIP-249) compatible.

See also: https://peps.python.org/pep-0249/#connection-objects

Parameters

  • db – The database for which you want to get the database object (either an alias or a dbs.DB object)

  • **kargs – Optional arguments.

mara_db.dbs.cursor_context(db: Union[str, mara_db.dbs.DB]) → object

A single iteration with a cursor context. When the iteration is closed, a commit is executed on the cursor.

Example usage:
with db.cursor_context() as c:

c.execute(‘UPDATE table SET table.c1 = 1 WHERE table.id = 5’)

Auto migration

mara_db.auto_migration.auto_migrate(engine: sqlalchemy.engine.base.Engine, models: List[sqlalchemy.sql.schema.MetaData])

Compares a database with a list of defined orm models and applies the diff. Prints executed SQL statements to stdout.

Based on alembic automigrations, but doesn’t require intermediate migration files.

Use with care, does not work in many cases.

Parameters

  • engine – the database to use

  • models – A list of orm models

Returns

True in case of no failures

mara_db.auto_migration.auto_discover_models_and_migrate() → bool

Auto-migrates all sqlalchemy models that been marked for auto-migration database with the alias ‘mara’.

Models are marked for auto-migration by being put into a module-level MARA_AUTOMIGRATE_SQLALCHEMY_MODELS variable. E.g.

MARA_AUTOMIGRATE_SQLALCHEMY_MODELS = [MyModel]

For this, all modules that contain sqlalchemy models need to be loaded first

Returns

True when no failure happened

Shell

mara_db.shell.query_command(db: object, timezone: str = None, echo_queries: bool = None) → str

Creates a shell command that receives a sql query from stdin and executes it

Parameters

  • db – The database in which to run the query (either an alias or a dbs.DB object

  • timezone – Sets the timezone of the client, if applicable

  • echo_queries – Whether the client should print executed queries, if applicable

Returns

A shell command string

Examples

>>> print(query_command('mara', 'America/New_York'))
PGTZ=America/New_York PGOPTIONS=--client-min-messages=warning psql --username=root --host=localhost             --echo-all --no-psqlrc --set ON_ERROR_STOP=on mara

>>> print(query_command(dbs.MysqlDB(host='localhost', database='test')))
mysql --default-character-set=utf8mb4 --host=localhost test
mara_db.shell.copy_to_stdout_command(db: object, header: bool = None, footer: bool = None, delimiter_char: str = None, csv_format: bool = None, pipe_format: mara_db.formats.Format = None) → str

Creates a shell command that receives a query from stdin, executes it and writes the output to stdout

Parameters

  • db – The database in which to run the query (either an alias or a dbs.DB object

  • header – Whether a csv header with the column name(s) will be included or not. No header, by default. (not implemented in sqsh for SQLServerDB)

  • footer – Whether a footer will be included or not. False by default. (Only implemented for PostgreSQLDB)

  • delimiter_char – str to delimit the fields in one row. Default: tab character

  • csv_format – Double quote ‘difficult’ strings (Only implemented for PostgreSQLDB)

  • pipe_format – The format passed to stdout

Returns

The composed shell command

Example

>>> print(copy_to_stdout_command(dbs.PostgreSQLDB(host='localhost', database='test')))
PGTZ=Europe/Berlin PGOPTIONS=--client-min-messages=warning psql --host=localhost  --no-psqlrc --set ON_ERROR_STOP=on test --tuples-only --no-align --field-separator='  '             | grep -a -v -e '^$'
mara_db.shell.copy_from_stdin_command(db: object, target_table: str, csv_format: bool = None, skip_header: bool = None, delimiter_char: str = None, quote_char: str = None, null_value_string: str = None, timezone: str = None, pipe_format: mara_db.formats.Format = None) → str

Creates a shell command that receives data from stdin and writes it to a table.

Options are tailored for the PostgreSQL COPY FROM STDIN command, adaptions might be needed for other databases. https://www.postgresql.org/docs/current/static/sql-copy.html

Parameters

  • db – The database to use (either an alias or a dbs.DB object

  • target_table – The table in which the data is written

  • csv_format – Treat the input as a CSV file (comma separated, double quoted literals)

  • skip_header – When true, skip the first line

  • delimiter_char – The character that separates columns

  • quote_char – The character for quoting strings

  • null_value_string – The string that denotes NULL values

  • timezone – Sets the timezone of the client, if applicable

  • pipe_format – The format passed from stdin

Returns

The composed shell command

Example

>>> print(copy_from_stdin_command('mara', target_table='foo'))
PGTZ=Europe/Berlin PGOPTIONS=--client-min-messages=warning psql --username=root --host=localhost             --echo-all --no-psqlrc --set ON_ERROR_STOP=on mara             --command="COPY foo FROM STDIN WITH CSV"
mara_db.shell.copy_command(source_db: object, target_db: object, target_table: str, timezone=None, csv_format=None, delimiter_char=None, pipe_format: mara_db.formats.Format = None) → str

Creates a shell command that - receives a sql query from stdin - executes the query in source_db - writes the results of the query to target_table in target_db

Parameters

  • source_db – The database in which to run the query (either an alias or a dbs.DB object

  • target_db – The database where to write the query results (alias or db configuration)

  • target_table – The table in which to write the query results

  • timezone – Sets the timezone of the client, if applicable

  • csv_format – double quote ‘difficult’ strings

  • delimiter_char – The character that separates columns, default ‘ ‘

  • pipe_format – The piping data format to be used

Returns

A shell command string

Example

>>> print(copy_command(dbs.SQLServerDB(database='source_db'), dbs.PostgreSQLDB(database='target_db'),                                 'target_table'))
sqsh  -D source_db -m csv           | PGTZ=Europe/Berlin PGOPTIONS=--client-min-messages=warning psql --echo-all --no-psqlrc                --set ON_ERROR_STOP=on target_db                --command="COPY target_table FROM STDIN WITH CSV HEADER"

SQLAlchemy

mara_db.sqlalchemy_engine.engine(db: object) → sqlalchemy.engine.base.Engine

Returns a sql alchemy engine for a configured database connection

Parameters

db – The database to use (either an alias or a dbs.DB object

Returns

The generated sqlalchemy engine

Example

>>> print(engine('mara'))
Engine(postgresql+psycopg2://None@localhost/mara)