I would like to create a MySQL table with Pandas' to_sql function which has a primary key (it is usually kind of good to have a primary key in a mysql table) as so:
group_export.to_sql(con = db, name = config.table_group_export, if_exists = 'replace', flavor = 'mysql', index = False)
but this creates a table without any primary key, (or even without any index).
The documentation mentions the parameter 'index_label' which combined with the 'index' parameter could be used to create an index but doesn't mention any option for primary keys.
Disclaimer: this answer is more experimental then practical, but maybe worth mention.
I found that class
pandas.io.sql.SQLTable
has named argumentkey
and if you assign it the name of the field then this field becomes the primary key:Unfortunately you can't just transfer this argument from
DataFrame.to_sql()
function. To use it you should:create
pandas.io.SQLDatabase
instancedefine function analoguous to
pandas.io.SQLDatabase.to_sql()
but with additional*kwargs
argument which is passed topandas.io.SQLTable
object created inside it (i've just copied originalto_sql()
method and added*kwargs
):call this function with your
SQLDatabase
instance and the dataframe you want to saveAnd we get something like
in the database.
PS You can of course monkey-patch
DataFrame
,io.SQLDatabase
andio.to_sql()
functions to use this workaround with convenience.