sqlalchemy, setting MySQL charset as `create_engine` argument

Question

I create sqlalchemy engine connecting to MySQL database. I want to specify charset as create_engine argument.

If I use create_engine liKe that:

create_engine('mysql+mysqldb://pd:pd@localhost/pd?charset=utf8') 

then all is fine. But, when I use it like that:

create_engine('mysql+mysqldb://pd:pd@localhost/pd', charset='utf8') 

then I get the following error:

TypeError: Invalid argument(s) 'charset' sent to create_engine(), using
    configuration MySQLDialect_mysqldb/QueuePool/Engine. Please check that
    the keyword arguments are appropriate for this combination of components.

According to the documentation, this usage should be possible:

The string form of the URL is dialect+driver://user:password@host/dbname[?key=value..] ...

**kwargs takes a wide variety of options which are routed towards their appropriate components. Arguments may be specific to the Engine, the underlying Dialect, as well as the Pool. Specific dialects also accept keyword arguments that are unique to that dialect. Here, we describe the parameters that are common to most create_engine() usage.

Why I cannot specify charset separately?

Answer 1

the additional DBAPI arguments, when passed separately, are passed via connect_args.

Answer 2

You can use sqlalchemy.engine.url.URL class

import os
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL

DB = {
    'drivername': 'mysql',
    'host': '127.0.0.1',
    'port': '3306',
    'username': os.environ['DBUNAME'],
    'password': os.environ['DBPASS'],
    'database': os.environ['DBNAME']
}

engine = create_engine(URL(**DB), connect_args={'charset':'utf8'})

Or even better

DB = {
    'drivername': 'mysql',
    'host': '127.0.0.1',
    'port': '3306',
    'username': os.environ['DBUNAME'],
    'password': os.environ['DBPASS'],
    'database': os.environ['DBNAME'],
    'query': {'charset':'utf8'}
}

engine = create_engine(URL(**DB))

According to the docs URL class has the following parameters

• drivername – the name of the database backend. This name will correspond to a module in sqlalchemy/databases or a third party plug-in.
• username – The user name.
• password – database password.
• host – The name of the host.
• port – The port number.
• database – The database name.
• query – A dictionary of options to be passed to the dialect and/or the DBAPI upon connect.