Can I export the Python Pandas framework to MS SQL?

Question

I am using pymssql and Pandas sql package to load data from SQL into Pandas dataframe using frame_query.

I would like to send it back to the SQL database using write_frame, but I was not able to find a lot of documentation on this. In particular, the flavor = 'sqlite' parameter exists. Does this mean that so far Pandas can only export SQLite? My company uses MS SQL Server 2008, so I need to export to it.

Answer 1

Unfortunately yes. sqlite is currently 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 pretty simple. For example, something like this might work (unverified!):

 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() 

Answer 2

Just to save someone who tried to use this for a while. It turns out the line:

 wildcards = ','.join(['?'] * len(frame.columns)) 

it should be:

 wildcards = ','.join(['%s'] * len(frame.columns)) 

Hope that helps