CODEWITHSUNDEEP
sqlalchemy
Herb21
Herb21

Reputation: 365

how to search for the existence of a database with sqlalchemy

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;

Upvotes: 28

Views: 31662

Answers (8)

StumpCore
StumpCore

Reputation: 1

You could just check with the default import os if the file exists.

import os
database_uri = "C:\\"
name_of_db = "Random.db"
db_exists = os.path.isfile(database_uri + name_of_db)

Upvotes: -1

Damien
Damien

Reputation: 1724

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
    # ...

Upvotes: 28

krema
krema

Reputation: 1231

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)

Upvotes: 0

csaroff
csaroff

Reputation: 132

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))

Upvotes: 0

mazzi
mazzi

Reputation: 86

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

Upvotes: 2

Adrian Saldanha
Adrian Saldanha

Reputation: 261

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

Upvotes: 26

Bryant Kou
Bryant Kou

Reputation: 1739

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))

Upvotes: 4

jd.
jd.

Reputation: 10958

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.

Upvotes: 4

Related Questions