Can I export a Python Pandas dataframe to MS SQL?

pandas, python, sql, sql-server-2008

Solution

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

Problem

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.

Original source