Menu
Is it possible to get a list of all databases in SQLAlchemy? I need a cross-database solution, so basic "SHOW DATABASES" doesn't cut it.
Similar to MetaData/Inspector capabilities to show all tables and columns.
558
Supported Databases. SQLAlchemy includes dialects for SQLite, Postgresql, MySQL, Oracle, MS-SQL, Firebird, Sybase and others, most of which support multiple DBAPIs.
Creating and Inserting Data into TablesBy passing the database which is not present, to the engine then sqlalchemy automatically creates a new database.
One of the key aspects of any data science workflow is the sourcing, cleaning, and storing of raw data in a form that can be used upstream. This process is commonly referred to as “Extract-Transform-Load,” or ETL for short.
The SQL Expression Language constructs its expressions against table columns. SQLAlchemy Column object represents a column in a database table which is in turn represented by a Tableobject. Metadata contains definitions of tables and associated objects such as index, view, triggers, etc.
Supported Databases SQLAlchemy includes dialects for SQLite, Postgresql, MySQL, Oracle, MS-SQL, Firebird, Sybase and others, most of which support multiple DBAPIs. Other dialects are published as external projects. The corresponding DB-API 2.0 implementation (or sometimes one of several available) is required to use each particular database.
Within SQLAlchemy, this is nothing more than a string name which is associated with a Table object, and is then rendered into SQL statements in a manner appropriate to the target database such that the table is referred towards in its remote “schema”, whatever mechanism that is on the target database.
The first step is to establish a connection with your existing database, using the create_engine() function of SQLAlchemy. Syntax: from sqlalchemy import create_engine engine = create_engine(dialect+driver://username:password@host:port/database) Explanation: dialect – Name of the DBMS
from sqlalchemy import MetaData metadata_obj = MetaData() MetaData is a container object that keeps together many different features of a database (or multiple databases) being described. To represent a table, use the Table class. Its two primary arguments are the table name, then the MetaData object which it will be associated with.
Looks like I found the answer myself.
There is a method called get_schema_names in Inspector class, which is not very well documented, but returns list of databases (just tested with MySQL and Postgres).
Usage:
import sqlalchemy as sa
engine = sa.create_engine('mysql+pymysql://user:pwd@localhost')
insp = sa.inspect(engine)
db_list = insp.get_schema_names()
print(db_list)
engine.execute('SELECT datname FROM pg_database;').fetchall()
Every entity in DB is saved in some "system" table. For example, in Postgres you can get all DBs from pg_database table.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
