howto create db 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)) 

Answer 1

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.

Answer 2

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.