python to sql_Python Pandas to_sql，如何用主键创建表？

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 argument key 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 instance

engine = sa.create_engine('postgresql:///somedb')

pandas_sql = pd.io.sql.pandasSQL_builder(engine, schema=None, flavor=None)

define function analoguous to pandas.io.SQLDatabase.to_sql() but with additional *kwargs argument which is passed to pandas.io.SQLTable object created inside it (i've just copied original to_sql() method and added *kwargs):

def to_sql_k(self, frame, name, if_exists='fail', index=True,

index_label=None, schema=None, chunksize=None, dtype=None, **kwargs):

if dtype is not None:

from sqlalchemy.types import to_instance, TypeEngine

for col, my_type in dtype.items():

if not isinstance(to_instance(my_type), TypeEngine):

raise ValueError('The type of %s is not a SQLAlchemy '

'type ' % col)

table = pd.io.sql.SQLTable(name, self, frame=frame, index=index,

if_exists=if_exists, index_label=index_label,

schema=schema, dtype=dtype, **kwargs)

table.create()

table.insert(chunksize)

call this function with your SQLDatabase instance and the dataframe you want to save

to_sql_k(pandas_sql, df2save, 'tmp',

index=True, index_label='id', keys='id', if_exists='replace')

And we get something like

CREATE TABLE public.tmp

(

id bigint NOT NULL DEFAULT nextval('tmp_id_seq'::regclass),

...

)

in the database.

PS You can of course monkey-patch DataFrame, io.SQLDatabase and io.to_sql() functions to use this workaround with convenience.