I am using pymssql and the Pandas sql package to load data from SQL into a Pandas dataframe with frame_query.
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.
Unfortunately, yes. At the moment sqlite is the only "flavor" supported by write_frame. See 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_frame should be fairly easy, though. For example, something like this might work (untested!):
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()
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
