Menu
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.
Documentation
314
Primary Key When creating a table, you should also create a column with a unique key for each record. This can be done by defining a PRIMARY KEY. We use the statement "INT AUTO_INCREMENT PRIMARY KEY" which will insert a unique number for each record. Starting at 1, and increased by one for each record.
Pandas Dataframes generally have an "index", one column of a dataset that gives the name for each row. It works like a primary key in a database table. But Pandas also supports a MultiIndex, in which the index for a row is some composite key of several columns.
Pandasql is a python library that allows manipulation of a Pandas Dataframe using SQL. Under the hood, Pandasql creates an SQLite table from the Pandas Dataframe of interest and allow users to query from the SQLite table using SQL.
Simply add the primary key after uploading the table with pandas.
group_export.to_sql(con=engine, name=example_table, if_exists='replace', flavor='mysql', index=False) with engine.connect() as con: con.execute('ALTER TABLE `example_table` ADD PRIMARY KEY (`ID_column`);') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
