{ "info": { "author": "Mara contributors", "author_email": "", "bugtrack_url": null, "classifiers": [], "description": "# Mara DB\n\n[![Build Status](https://travis-ci.org/mara/mara-db.svg?branch=master)](https://travis-ci.org/mara/mara-db)\n![PyPI - License](https://img.shields.io/pypi/l/mara-db.svg)\n[![PyPI version](https://badge.fury.io/py/mara-db.svg)](https://badge.fury.io/py/mara-db)\n[![Slack Status](https://img.shields.io/badge/slack-join_chat-white.svg?logo=slack&style=social)](https://communityinviter.com/apps/mara-users/public-invite)\n\nMini package for configuring and accessing multiple databases in a single project. Decouples the use of databases and their configuration by using \"aliases\" for databases.\n \nThe file [mara_db/dbs.py](mara_db/dbs.py) contains abstract database configurations for PostgreSQL, Mysql, SQL Server, Oracle and SQLite. The database connections of a project are configured by overwriting the `databases` function in [mara_db/config.py](mara_db/config.py):\n\n```python\nimport mara_db.config\nimport mara_db.dbs\n\n## configure database connections for different aliases\nmara_db.config.databases = lambda: {\n 'mara': mara_db.dbs.PostgreSQLDB(host='localhost', user='root', database='mara'),\n 'dwh': mara_db.dbs.PostgreSQLDB(database='dwh'),\n 'source-1': mara_db.dbs.MysqlDB(host='some-localhost', database='my_app', user='dwh'),\n 'source-2': mara_db.dbs.SQLServerDB(user='dwh_read', password='123abc', database='db1', host='some-sql-server')\n}\n\n## access individual database configurations with `dbs.db`:\nprint(mara_db.dbs.db('mara'))\n# -> \n```\n\n \n\n\n## Visualization of (PostgreSQL) database schemas \n\n[mara_db/views.py](mara_db/views.py) contains a schema visualization for all configured databases using graphviz (currently PostgreSQL only). It basically show tables of selected schemas together with the foreign key relations between them. \n\n\n![Schema visualization](https://github.com/mara/mara-db/raw/master/docs/schema-visualization.png)\n\nFor finding missing foreign key constraints, columns that follow a specific naming pattern (configurable via `config.schema_ui_foreign_key_column_regex`, default `*_fk`) and that are not part of foreign key constraints are drawn in pink. \n\n \n\n\n## Fast batch processing: Accessing databases with shell commands\n\nThe file [mara_db/shell.py](mara_db/shell.py) contains functions that create commands for accessing databases via their command line clients. \n \nFor example, the `query_command` function creates a shell command that can receive an SQL query from stdin and execute it:\n\n```python\nimport mara_db.shell\n\nprint(mara_db.shell.query_command('source-1'))\n# -> mysql --default-character-set=utf8mb4 --user=dwh --host=some-localhost my_app\n\nprint(mara_db.shell.query_command('dwh', timezone='Europe/Lisbon', echo_queries=False))\n# -> PGTZ=Europe/Lisbon PGOPTIONS=--client-min-messages=warning psql --no-psqlrc --set ON_ERROR_STOP=on dwh\n```\n\nThe function `copy_to_stdout_command` creates a shell command that receives a query on stdin and writes the result to stdout in tabular form:\n\n```python\nprint(mara_db.shell.copy_to_stdout_command('source-1'))\n# -> mysql --default-character-set=utf8mb4 --user=dwh --host=some-localhost my_app --skip-column-names\n```\n\nSimilarly, `copy_from_stdin_command` creates a client command that receives tabular data from stdin and and writes it to a target table: \n\n```python\nprint(mara_db.shell.copy_from_stdin_command('dwh', target_table='some_table', delimiter_char=';'))\n# -> PGTZ=Europe/Berlin PGOPTIONS=--client-min-messages=warning psql --echo-all --no-psqlrc --set ON_ERROR_STOP=on dwh \\\n# --command=\"COPY some_table FROM STDIN WITH DELIMITER AS ';'\"\n```\n\nFinally, `copy_command` creates a shell command that receives a sql query from stdin, executes the query in `source_db` and then writes the result of to `target_table` in `target_db`:\n\n```python\nprint(mara_db.shell.copy_command('source-2', 'dwh', target_table='some_table'))\n# -> sed 's/\\\\\\\\$/\\$/g;s/\\$/\\\\\\\\$/g' \\\n# | sqsh -U dwh_read -P 123abc -S some-sql-server -D db1 -m csv \\\n# | PGTZ=Europe/Berlin PGOPTIONS=--client-min-messages=warning psql --echo-all --no-psqlrc --set ON_ERROR_STOP=on dwh \\\n# --command = \"COPY some_table FROM STDIN WITH CSV HEADER\"\n```\n\n \n\n\nThe following **command line clients** are used to access the various databases:\n\n| Database | Client binary | Comments | \n| --- | --- | --- |\n| Postgresql | `psql` | Included in standard distributions. |\n| MariaDB / Mysql | `mysql` | Included in standard distributions. |\n| SQL Server | `sqsh` | From [https://sourceforge.net/projects/sqsh/](https://sourceforge.net/projects/sqsh/), usually messy to get working. On ubuntu, use [http://ppa.launchpad.net/jasc/sqsh/ubuntu/](http://ppa.launchpad.net/jasc/sqsh/ubuntu/) backport. On Mac, try the homebrew version or install from source. |\n| Oracle | `sqlplus64` | See the [Oracle Instant Client](https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html) homepage for details. On Mac, follow [these instructions](https://vanwollingen.nl/install-oracle-instant-client-and-sqlplus-using-homebrew-a233ce224bf). Then ` sudo ln -s /usr/local/bin/sqlplus /usr/local/bin/sqlplus64` to make the binary accessible as `sqlplus64`. |\n| SQLite | `sqlite3` | Available in standard distributions. Version >3.20.x required (not the case on Ubuntu 14.04). |\n\n \n\n\n## Make it so! Auto-migration of SQLAlchemy models\n\n[Alembic has a feature](http://alembic.zzzcomputing.com/en/latest/autogenerate.html) that can create a diff between the state of a database and the ORM models of an application. This feature is used in [mara_db/auto_migrate.py](mara_db/auto_migrate.py) to automatically perform all necessary database transformations, without intermediate migration files:\n\n```python\n# define a model / table\nclass MyTable(sqlalchemy.ext.declarative.declarative_base()):\n __tablename__ = 'my_table'\n my_table_id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)\n column_1 = sqlalchemy.Column(sqlalchemy.TEXT, nullable=False, index=True)\n\n\ndb = mara_db.dbs.SQLiteDB(file_name='/tmp/test.sqlite')\n\n# create database and table \nmara_db.auto_migration.auto_migrate(engine=mara_db.auto_migration.engine(db), models=[MyTable])\n# ->\n# Created database \"sqlite:////tmp/test.sqlite\"\n#\n# CREATE TABLE my_table (\n# my_table_id SERIAL NOT NULL,\n# column_1 TEXT NOT NULL,\n# PRIMARY KEY (my_table_id)\n# );\n#\n# CREATE INDEX ix_my_table_column_1 ON my_table (column_1);\n```\n\nWhen the model is changed later, then `auto_migrate` creates a diff against the existing database and applies it:\n\n```python \n# remove index and add another column\nclass MyTable(sqlalchemy.ext.declarative.declarative_base()):\n __tablename__ = 'my_table'\n my_table_id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)\n column_1 = sqlalchemy.Column(sqlalchemy.TEXT, nullable=False)\n column_2 = sqlalchemy.Column(sqlalchemy.Integer)\n\nauto_migrate(engine=engine(db), models=[MyTable])\n# ->\n# ALTER TABLE my_table ADD COLUMN column_2 INTEGER;\n#\n# DROP INDEX ix_my_table_text_column_1;\n```\n\n**Use with care**! The are lot of changes [that alembic auto-generate can not detect](http://alembic.zzzcomputing.com/en/latest/autogenerate.html#what-does-autogenerate-detect-and-what-does-it-not-detect). We recommend testing each aut-migration on a staging system first before deploying to production. Sometimes manual migration scripts will be necessary.\n \n\n\n## Installation\n\n```bash\npip install mara-db\n```\n\nor\n\n```bash\npip install git+https://github.com/mara/mara-db.git\n```", "description_content_type": "text/markdown", "docs_url": null, "download_url": "", "downloads": { "last_day": -1, "last_month": -1, "last_week": -1 }, "home_page": "", "keywords": "", "license": "MIT", "maintainer": "", "maintainer_email": "", "name": "mara-db", "package_url": "https://pypi.org/project/mara-db/", "platform": "", "project_url": "https://pypi.org/project/mara-db/", "project_urls": null, "release_url": "https://pypi.org/project/mara-db/4.3.1/", "requires_dist": null, "requires_python": ">=3.6", "summary": "Configuration and monitoring of database connections", "version": "4.3.1" }, "last_serial": 5490157, "releases": { "4.2.0": [ { "comment_text": "", "digests": { "md5": "69bc1efe1e2365d6be5120a78bcc989d", "sha256": "fb1a930eebb9040d44a77949942f2699f009f04c9197f577f67389432c419ca0" }, "downloads": -1, "filename": "mara-db-4.2.0.tar.gz", "has_sig": false, "md5_digest": "69bc1efe1e2365d6be5120a78bcc989d", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 16524, "upload_time": "2019-07-04T14:40:38", "url": "https://files.pythonhosted.org/packages/d1/73/64564aed8f25afa48169cc9a36fbeaf1b81620a5b2baaadab310368eed06/mara-db-4.2.0.tar.gz" } ], "4.2.1": [ { "comment_text": "", "digests": { "md5": "0328fbf58f4fca14adff011472de4a6d", "sha256": "bd243680198c405ae3891c838df7f5018995c99c894ebde78a92fce7d74db1a4" }, "downloads": -1, "filename": "mara-db-4.2.1.tar.gz", "has_sig": false, "md5_digest": "0328fbf58f4fca14adff011472de4a6d", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 16583, "upload_time": "2019-07-04T16:36:04", "url": "https://files.pythonhosted.org/packages/d2/ef/bc54ed78dfb3c974b1e8ff715c414d393a1bc48f8fddf9f6ee42f73a77dd/mara-db-4.2.1.tar.gz" } ], "4.2.2": [ { "comment_text": "", "digests": { "md5": "ae1981444a5b94ace308e0bd6fc984b1", "sha256": "70506d70a881cd93e150aa858e4d83b07527ec3dfac100d991642fe581f4a36a" }, "downloads": -1, "filename": "mara-db-4.2.2.tar.gz", "has_sig": false, "md5_digest": "ae1981444a5b94ace308e0bd6fc984b1", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 16584, "upload_time": "2019-07-04T16:39:45", "url": "https://files.pythonhosted.org/packages/c4/94/b4d419a06c2af3dfb13ab9df95acec91842d1b1133633003dd55936e03c2/mara-db-4.2.2.tar.gz" } ], "4.3.0": [ { "comment_text": "", "digests": { "md5": "75fa69b53070569b2fa78e90960fb141", "sha256": "18288b62d9b72510ab82770a53d9e60dc5b6c8b8183e0e325b9945c8083dc7f8" }, "downloads": -1, "filename": "mara-db-4.3.0.tar.gz", "has_sig": false, "md5_digest": "75fa69b53070569b2fa78e90960fb141", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 16954, "upload_time": "2019-07-04T19:37:48", "url": "https://files.pythonhosted.org/packages/fe/34/6da110c97303740a0e2397332924fb868eb6915923fd6e8fd9822944f0f7/mara-db-4.3.0.tar.gz" } ], "4.3.1": [ { "comment_text": "", "digests": { "md5": "9de2fbe6e26a97a00eecc68df3edfe83", "sha256": "05099b1353119f7c22a87caa5d0008e6a44cdeea96f3a3d5c0e9faf7d8ef42ba" }, "downloads": -1, "filename": "mara-db-4.3.1.tar.gz", "has_sig": false, "md5_digest": "9de2fbe6e26a97a00eecc68df3edfe83", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 16949, "upload_time": "2019-07-05T08:01:53", "url": "https://files.pythonhosted.org/packages/71/db/99c722829bbf168ef4e7aa8ad5c32a53e11991b8eea2f26a543975c37762/mara-db-4.3.1.tar.gz" } ] }, "urls": [ { "comment_text": "", "digests": { "md5": "9de2fbe6e26a97a00eecc68df3edfe83", "sha256": "05099b1353119f7c22a87caa5d0008e6a44cdeea96f3a3d5c0e9faf7d8ef42ba" }, "downloads": -1, "filename": "mara-db-4.3.1.tar.gz", "has_sig": false, "md5_digest": "9de2fbe6e26a97a00eecc68df3edfe83", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 16949, "upload_time": "2019-07-05T08:01:53", "url": "https://files.pythonhosted.org/packages/71/db/99c722829bbf168ef4e7aa8ad5c32a53e11991b8eea2f26a543975c37762/mara-db-4.3.1.tar.gz" } ] }