Python Pandas to_sql,如何用主键创建表? [英] Python Pandas to_sql, how to create a table with a primary key?
问题描述
我想用Pandas的to_sql函数创建一个具有主键的MySQL表(在mysql表中具有主键通常是一种好习惯),如下所示:
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).
文档中提到了参数"index_label",该参数与"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.
我发现类pandas.io.sql.SQLTable
的命名参数为key
,如果您为其分配了字段名称,则该字段将成为主键:
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:
不幸的是,您不能只从DataFrame.to_sql()
函数传递此参数.要使用它,您应该:
Unfortunately you can't just transfer this argument from DataFrame.to_sql()
function. To use it you should:
-
创建
pandas.io.SQLDatabase
实例
engine = sa.create_engine('postgresql:///somedb')
pandas_sql = pd.io.sql.pandasSQL_builder(engine, schema=None, flavor=None)
定义类似于pandas.io.SQLDatabase.to_sql()
的函数,但带有附加的*kwargs
参数,该参数传递给在其中创建的pandas.io.SQLTable
对象(我刚刚复制了原始的to_sql()
方法并添加了*kwargs
): /p>
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)
使用您的SQLDatabase
实例和要保存的数据框调用此函数
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')
我们得到类似的东西
CREATE TABLE public.tmp
(
id bigint NOT NULL DEFAULT nextval('tmp_id_seq'::regclass),
...
)
在数据库中.
PS当然,您可以使用Monkey-patch DataFrame
,io.SQLDatabase
和io.to_sql()
函数来方便地使用此替代方法.
PS You can of course monkey-patch DataFrame
, io.SQLDatabase
and io.to_sql()
functions to use this workaround with convenience.
这篇关于Python Pandas to_sql,如何用主键创建表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!