How to create db in MySQL with SQLAlchemy?

Question

I need to create a db in MySQL using SQLAlchemy, I am able to connect to a db if it already exists, but I want to be able to create it if it does not exist. These are my tables:

    #def __init__(self):
Base = declarative_base()

class utente(Base):
    __tablename__="utente"
    utente_id=Column(Integer,primary_key=True)
    nome_utente=Column(Unicode(20))
    ruolo=Column(String(10))
    MetaData.create_all()

    def __repr(self):
        return "utente: {0}, {1}, id: {2}".format(self.ruolo,self.nome_utente,self.utente_id)
    
    
class dbmmas(Base):
    
    __tablename__="dbmmas"
    db_id=Column(Integer,primary_key=True,autoincrement=True)
    nome_db=Column(String(10))
    censimento=Column(Integer)
    versione=Column(Integer)
    ins_data=Column(DateTime)
    mod_data=Column(DateTime)
    ins_utente=Column(Integer)
    mod_utente=Column(Integer)
    MetaData.create_all()

    def __repr(self):
        return "dbmmas: {0}, censimento {1}, versione {2}".format(self.nome_db,self.censimento,self.versione)    
    
class funzione(Base):
    __tablename__="funzione"
    funzione_id=Column(Integer,primary_key=True,autoincrement=True)
    categoria=Column(String(10))
    nome=Column(String(20))
    def __repr__(self):
        return "funzione:{0},categoria:{1},id:{2} ".format(self.nome,self.categoria,self.funzione_id)
    
class profilo(Base):
    __tablename__="rel_utente_funzione" 
    utente_id=Column(Integer,primary_key=True)
    funzione_id=Column(Integer,primary_key=True)
    amministratore=Column(Integer)
    MetaData.create_all()
    
    def __repr(self):
        l=lambda x: "amministratore" if x==1 else "generico"
        return "profilo per utente_id:{0}, tipo: {1}, funzione_id: {2}".format(self.utente_id,l(self.amministratore),self.funzione_id)    
    
class aree(Base):
    __tablename__="rel_utente_zona"
    UTB_id=Column(String(10), primary_key=True) # "in realta' si tratta della seatureSignature della feature sullo shapefile"
    utente_id=Column(Integer, primary_key=True)
    amministratore=Column(Integer)
    MetaData.create_all()
    def __repr(self):
        l=lambda x: "amministratore" if x==1 else "generico"
        return "zona: {0}, pe utente_id:{1}, {2}".format(self.UTB_id,self.utente_id,l(self.amministratore))
    
class rel_utente_dbmmas(Base):
    __tablename__="rel_utente_dbmmas"
    utente_id=Column(Integer,primary_key=True)
    db_id=Column(Integer,primary_key=True)
    amministratore=(Integer)
    MetaData.create_all()
    def __repr(self):
        l=lambda x: "amministratore" if x==1 else "generico"
        return "dbregistrato: {0} per l'utente{1} {2}".format(self.db_id,self.utente_id,l(self.amministratore))

Thanks, now i can create the db, is there a way to check the existence of the dbin advance? I can only think to use try — arpho, Jun 02 '12 at 15:36

score 53 · Answer 1 · answered May 26 '12 at 23:28

53

To create a mysql database you just connect to the server an create the database:

import sqlalchemy
engine = sqlalchemy.create_engine('mysql://user:password@server') # connect to server
engine.execute("CREATE DATABASE dbname") #create db
engine.execute("USE dbname") # select new db
# use the new db
# continue with your work...

of course your user has to have the permission to create databases.

answered May 26 '12 at 23:28

mata

67,110
10
163
162

1

Don't forget the database API after the database server, using the format: "dialect+driver". For example, you could use "mysql+mysqldb". https://docs.sqlalchemy.org/en/13/dialects/mysql.html#module-sqlalchemy.dialects.mysql.mysqldb – rmutalik Dec 04 '19 at 07:51
how is this get upvoted? you missing 1 import. @rmutalik was right. it gave me an error – greendino May 30 '20 at 04:12
1

It should work if you have the MySQLdb driver installed (as it is the default if none is specified), otherwise yes, you need to specify the driver you want to use in the connection string. No other import should be needed. – mata Jun 03 '20 at 16:35
Downvote. Not explained what you need to `pip install` and that the start of the url depends on what you have installed. – questionto42 Jan 07 '22 at 15:58
These comments are a bit harsh: it's implicit in the original question that the asker already knows to connect, they just don't know how to create a database. – snakecharmerb Jan 08 '22 at 16:24

score 33 · Answer 2 · edited May 13 '19 at 23:43

33

You can use SQLAlchemy-Utils for that.

pip install sqlalchemy-utils

Then you can do things like

from sqlalchemy_utils import create_database, database_exists
url = 'mysql://{0}:{1}@{2}:{3}'.format(user, pass, host, port)
if not database_exists(url):
    create_database(url)

I found the answer here, it helped me a lot.

edited May 13 '19 at 23:43

Nate Anderson

18,334
18
100
135

answered Jul 05 '18 at 05:51

Xalio08

973
10
17

1

while this answer does require an additional package, i do like the simplicity and how modular it is. thanks! – beep_check Oct 27 '19 at 18:19
4

you might want to specify encoding if you like to as follows: `create_database(url, encoding='utf8mb4')` – navule Mar 04 '20 at 18:17

Bryant Kou · Answer 3 · 2016-02-25T01:39:12.937

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, and to create it if it doesn't exist.

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 6 · Answer 4 · answered Sep 07 '13 at 07:23

6

CREATE DATABASE IF NOT EXISTS dbName;

answered Sep 07 '13 at 07:23

ThinkCode

7,841
21
73
92

ATH · Answer 5 · 2021-04-26T22:09:33.757

2

Would recommend using with:

from sqlalchemy import create_engine

username = ''
password = ''
host = 'localhost'
port = 3306
DB_NAME = 'db_name'

engine = create_engine(f"mysql://{username}:{password}@{host}:{port}")

with engine.connect() as conn:
    # Do not substitute user-supplied database names here.
    conn.execute(f"CREATE DATABASE IF NOT EXISTS {DB_NAME}")

edited Apr 26 '21 at 22:09

answered Feb 25 '21 at 02:51

ATH

666
6
13

Wrapping the SQL statement in `text` resolves "Not an executable object". ``` from sqlalchemy import text conn.execute(text(f"....")) ``` – Yash Nag Sep 06 '22 at 09:11

questionto42 · Answer 6 · 2022-01-19T22:04:39.533

The mysqlclient seems to be up to 10 times faster in benchmark tests than PyMySQL, see: What's the difference between MySQLdb, mysqlclient and MySQL connector/Python?.

Yet, why not use a Python-ready package for Python, at least, if it is not about every second of query time? PyMySQL is suggested by the following links, for example:

Python packages:

Install with pip, at best put in "requirements.txt":

PyMySQL
SQLAlchemy

Again, if it is about the best speed of the query, use mysqlclient package. Then you need to install an additional Linux package with sudo apt-get install libmysqlclient-dev.

`import` statements

Only one needed:

import sqlalchemy

Connection string (= db_url)

Connection string starting with {dialect/DBAPI}+{driver}:

db_url = mysql+pymysql://

where pymysql stands for the used Python package "PyMySQL" as the driver.

Again, if it is about the best speed of the query, use mysqlclient package. Then you need mysql+msqldb:// at this point.

For a remote connection, you need to add to the connection string:

host
user
password
database
port (the port only if it is not the standard 3306)

You can create your db_url with several methods. Do not write user and password and at best any other variable value directly in the string to avoid possible attacks:

sqlalchemy.engine.URL.create(), or with .url.URL, see an example at Connecting from Cloud Functions to Cloud SQL or an example which automatically adds ? suffixes, for example ?driver=SQL+Server, at the end of the string at Building a connection URL for mssql+pyodbc with sqlalchemy.engine.url.URL
f"""...{my_var}..."""
"""...{my_var}...""".format(my_var=xyz_var)
...

Example without the url helper of SQLAlchemy:

db_url = "{dialect}+{driver}://{user}:{password}@{host}:{port}/{database}".format(

or:

db_url = "{dialect}+{driver}://{user}:{password}@{host}/{database}?host={host}?port={port}".format(
         dialect = 'mysql',
         driver = 'pymysql',
         username=db_user,
         password=db_pass,
         database=db_name,
         host=db_host,
         port=db_port
     )

Other engine configurations

For other connection drivers, dialects and methods, see the SQLAlchemy 1.4 Documentation - Engine Configuration

Create the db if not exists

See How to create a new database using SQLAlchemy?.

engine = sqlalchemy.create_engine(db_url)
if not sqlalchemy.database_exists(engine.url):
    create_database(engine.url)
with engine.connect() as conn:
    conn.execute("commit")
    conn.execute("create database test")

How to create db in MySQL with SQLAlchemy?

6 Answers6

Python packages:

`import` statements

Connection string (= db_url)

Other engine configurations

Create the db if not exists

Linked

How to create db in MySQL with SQLAlchemy?

6 Answers6

Python packages:

import statements

Connection string (= db_url)

Other engine configurations

Create the db if not exists

Linked

`import` statements