SQLAlchemy and the sql 'Use Database' command

Question

I am using sqlalchemy and the create_engine to connect to mysql, build a database and start populating with relevant data.

edit, to preface, the database in question needs to be first created. to do this I perform the following commands

database_address = 'mysql+pymysql://{0}:{1}@{2}:{3}'
database_address = database_address.format('username',
                                           'password',
                                           'ip_address',
                                           'port')
engine = create_engine(database_address, echo=False)

Database_Name = 'DatabaseName'
engine.execute(("Create Databse {0}").format(Database_Name)

Following creation of the database, I try to perform a 'use' command but end up receiving the following error

line 3516, in _escape_identifier
    value = value.replace(self.escape_quote, self.escape_to_quote)

AttributeError: 'NoneType' object has no attribute 'replace'

I traced the error to a post which stated that this occurs when using the following command in python 3

engine.execute("USE dbname")

What else needs to be included in the execute command to access the mysql database and not throw an error.

match · Accepted Answer · 2020-05-18 09:49:58Z

2

You shouldn't use the USE command - instead you should specify the database you wish to connect to in the create_engine connection url - i.e.:

database_address = 'mysql+pymysql://{0}:{1}@{2}:{3}/{4}'
database_address = database_address.format('username',
                                           'password',
                                           'ip_address',
                                           'port',
                                           'database')
engine = create_engine(database_address, echo=False)

The create_engine docs are here: https://docs.sqlalchemy.org/en/13/core/engines.html#mysql

answered May 18, 2020 at 9:49

match

11.2k3 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hojo.Timberwolf Over a year ago

but the database hasn't been created yet. I have this setup for when the database exists but how does one go about before.

match Over a year ago

You should be using sqlalchemy to create the database via Base.metadata.create_all(engine): docs.sqlalchemy.org/en/13/orm/extensions/declarative/…

Hojo.Timberwolf · Accepted Answer · 2020-05-19 07:56:15Z

1

Figured out what to do,

Following advise from Match, I looked into SQLAlchemy and it's ability to create schema.

Found the following code

from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database

database_address = 'mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8mb4'
database_address = database_address.format('username','password','address','port','DB')
engine = create_engine(database_address, echo=False)

if not database_exists(self.engine.url):
        create_database(self.engine.url)

So creating an engine with the Schema name identified, I can use the utility database_exists to see if the database does exist, if not, then create using the create_database function.

answered May 19, 2020 at 7:56

Hojo.Timberwolf

1,1112 gold badges14 silver badges41 bronze badges

1 Comment

MrYutz Over a year ago

You shouldn't have self.engine.url in this context. Just use database_address instead.

Collectives™ on Stack Overflow

SQLAlchemy and the sql 'Use Database' command

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related