我可以将 Python Pandas 数据框导出到 MS SQL 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11314693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I export a Python Pandas dataframe to MS SQL?
提问by David M
I am using pymssqland the Pandas sql package to load data from SQL into a Pandas dataframewith frame_query.
我正在使用pymssqlPandas sql 包将数据从 SQL 加载到dataframe带有 frame_query的 Pandas 中。
I would like to send it back to the SQL database using write_frame, but I haven't been able to find much documentation on this. In particular, there is a parameter flavor='sqlite'. Does this mean that so far Pandas can only export to SQLite? My firm is using MS SQL Server 2008 so I need to export to that.
我想使用 write_frame 将它发送回 SQL 数据库，但我找不到太多关于此的文档。特别是，有一个参数 flavor='sqlite'。这是否意味着到目前为止 Pandas 只能导出到 SQLite？我的公司正在使用 MS SQL Server 2008，所以我需要导出到那个。
采纳答案by lbolla
Unfortunately, yes. At the moment sqliteis the only "flavor" supported by write_frame. See https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L155
不幸的是，是的。目前sqlite是唯一支持的“口味” write_frame。见https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L155
def write_frame(frame, name=None, con=None, flavor='sqlite'):
"""
Write records stored in a DataFrame to SQLite. The index will currently be
dropped
"""
if flavor == 'sqlite':
schema = get_sqlite_schema(frame, name)
else:
raise NotImplementedError
Writing a simple write_frameshould be fairly easy, though. For example, something like this might work (untested!):
不过，写一个简单的write_frame应该相当容易。例如，这样的事情可能会起作用（未经测试！）：
import pymssql
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase')
cur = conn.cursor()
# frame is your dataframe
wildcards = ','.join(['?'] * len(frame.columns))
data = [tuple(x) for x in frame.values]
table_name = 'Table'
cur.executemany("INSERT INTO %s VALUES(%s)" % (table_name, wildcards), data)
conn.commit()
回答by Rian
Just to save someone else who tried to use this some time. It turns out the line:
只是为了拯救曾经尝试使用它的其他人。原来这行：
wildcards = ','.join(['?'] * len(frame.columns))
should be:
应该：
wildcards = ','.join(['%s'] * len(frame.columns))
Hope that helps
希望有帮助
