how to search for the existence of a database with sqlalchemy

Question

I need to write a script with python sqlalchemy that searchs if a database exist, if the database exists it should query the database else create database and tables.

Pseudocode:

   if db exists cursor.execute(sql)
   else
      create db test;
      create tables;
      insert data;

score 27 · Answer 1 · edited Sep 19 '21 at 03:43

You can use the sqlalchemy.engine.base.Engine.connect() method, which will raise a OperationalError if the database does not exist.

import sqlalchemy as sqla
from sqlalchemy import create_engine
from sqlalchemy.exc import OperationalError
db = sqla.create_engine(database_uri)
try:
    db.connect()
    db.execute(sql)
except OperationalError:
    # Switch database component of the uri
    default_database_uri = os.path.join(os.path.dirname(
                           str(db.engine.url)), 'mysql')
    db = sqla.create_engine(default_database_uri)
    # Create your missing database/tables/data here
    # ...

connect() doesn't raise an exception, instead it just creates and empty one — geniaz1, Jul 28 '20 at 15:44

score 27 · Answer 2 · edited Oct 03 '17 at 17:36

27

An alternate way if you don't mind to import other libs is to use sqlalchemy_utils. Then database_exists does what you expect, which you may pass the sqlalchemy database uri.

if database_exists('sqllite:////tmp/test.db'):
    do_stuff_with_db(db)

http://sqlalchemy-utils.readthedocs.org/en/latest/database_helpers.html

edited Oct 03 '17 at 17:36

Code-Apprentice

81,660
23
145
268

answered Jan 27 '15 at 23:29

Adrian Saldanha

271
3
2

2

This approach doesn't seem to work in Python 3.8 yet – dslosky Apr 06 '21 at 01:46
1

It works for me with Python 3.8, but _not_ if the SQLite DB lives somwhere under `/tmp` – swimmer Nov 19 '21 at 14:37

score 4 · Answer 3 · answered Feb 26 '13 at 09:33

4

Create an engine that connects to the database and executes a universal query like SELECT 1;. If it fails, you can create the DB. How to create the new database depends on the DBMS though.

With PostgreSQL you would connect to the postgres database and issue a CREATE DATABASE statement, then connect to the newly created database.

answered Feb 26 '13 at 09:33

jd.

10,678
3
46
55

i have a pool of database already in existance so if i create a universal query it will pick those and not create the one i want. I was hoping that there is an sqlalchemy equivalent to mysql if not exist create database query. – Herb21 Feb 26 '13 at 09:46
1

By "universal" I mean it works on all SQL databases - I don't know which backend(s) you're using. `create_engine(my_target_dsn).execute('select 1')` won't interfere with your existing pool. – jd. Feb 26 '13 at 09:51
my bad i get you i thought of that but it seemed so plain and easy i didnt think it would work. by the way im using python and sqlalchemy – Herb21 Feb 26 '13 at 09:55

Bryant Kou · Answer 4 · 2016-02-25T01:39:34.847

I don't know what the canonical way is but here's a way to check to see if a database exists by checking against the list of databases.

from sqlalchemy import create_engine

# This engine just used to query for list of databases
mysql_engine = create_engine('mysql://{0}:{1}@{2}:{3}'.format(user, pass, host, port))

# Query for existing databases
existing_databases = mysql_engine.execute("SHOW DATABASES;")
# Results are a list of single item tuples, so unpack each tuple
existing_databases = [d[0] for d in existing_databases]

# Create database if not exists
if database not in existing_databases:
    mysql_engine.execute("CREATE DATABASE {0}".format(database))
    print("Created database {0}".format(database))

# Go ahead and use this engine
db_engine = create_engine('mysql://{0}:{1}@{2}:{3}/{4}'.format(user, pass, host, port, db))

Here's an alternative method if you don't need to know if the database was created or not.

from sqlalchemy import create_engine

# This engine just used to query for list of databases
mysql_engine = create_engine('mysql://{0}:{1}@{2}:{3}'.format(user, pass, host, port))

# Query for existing databases
mysql_engine.execute("CREATE DATABASE IF NOT EXISTS {0} ".format(database))

# Go ahead and use this engine
db_engine = create_engine('mysql://{0}:{1}@{2}:{3}/{4}'.format(user, pass, host, port, db))

score 2 · Answer 5 · answered Apr 17 '20 at 16:46

2

You can use https://sqlalchemy-utils.readthedocs.io/en/latest/ There's a helper called database_exists()

answered Apr 17 '20 at 16:46

mazzi

86
1
5

score 0 · Answer 6 · answered May 08 '20 at 19:05

If you do decide to go with sqlalchemy_utils, make sure that you apply your connect_args as a querystring. Otherwise those args won't be carried over when the database_exists function recreates the sqlalchemy engine

from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists

connect_args = {'charset': 'utf8'}
connect_args['ssl_cert'] = 'certs/client-cert.pem'
connect_args['ssl_key'] = 'certs/client-key.pem'
connect_args['ssl_ca'] = 'certs/server-ca.pem'

engine = create_engine(os.environ['MYSQL_CONN_URL'] + '/' + os.environ['DB_NAME'] + '?' +  urlencode(connect_args))
print(database_exists(engine.url))

score 0 · Answer 7 · answered Nov 09 '20 at 18:35

Install the pip package

pip install sqlalchemy_utils

Check with database_exists

from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists

engine = create_engine('postgresql://postgres@localhost/name')
database_exists(engine.url)

score 0 · Answer 8 · edited Oct 31 '22 at 18:30

Since, this thread doesn't specifically mention a preferred DB flavor, I going to add a not about the MSSQL support in SQLAlchemy.

Until this ticket gets closed, database_exist would throw an error that the database doesn't exist, which defies the whole point of having this function. Therefore, you would need to find a temporary workaround, maybe creating the database when you are spinning your Docker container or smth. similar.

Also, here is an SO Post listing 7 different alternatives how to check that a DB exists: how to search for the existence of a database with sqlalchemy

how to search for the existence of a database with sqlalchemy

8 Answers8

Linked