Can I export a Python Pandas dataframe to MS SQL?

Question

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.

Answer 1

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