Load a dataframe to SQL Server from pandas?

Asked Apr 12 '17 at 13:55

Active Apr 12 '17 at 14:11

Viewed 2,489 times

I am trying to test a script that will push a dataframe to SQL Server.

Here is what I have so far:

import pandas as pd
import pyodbc

conn_str = (
    r'Driver={SQL Server};'
    r'SERVER=wouldntuliketoknow\SQLEXPRESS;'
    r'Database=test;'
    r'Trusted_Connection=yes;'
    )
cnxn = pyodbc.connect(conn_str)
cursor = cnxn.cursor()

globalID = [9581091, 9581091, 79735371, 79735371, 79735371]
ID = [53506312, 961273620, 53506312, 79735371, 135962137]
names = ['meh',
        'cool',
        'dude whatever',
        'foo',
        'bar']

df = pd.DataFrame({'globalID': globalID, 'ID': ID, 'name': names})
df.to_sql("pandas", cnxn)
print(conn_str)

It is throwing an error:

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW)")

What am I doing wrong?

I'm not sure where the sqllite_master is coming from in the error?

Is the cursor necessary?

edited Apr 12 '17 at 14:11

marc_s

732,580
175
1,330
1,459

asked Apr 12 '17 at 13:55

spitfiredd

2,897
5
32
75

"SQLite is the only supported option if SQLAlchemy is not installed." per http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html – mechanical_meat Apr 12 '17 at 13:58
Well I have SQLAlchemy install, what do I need to add to my script? – spitfiredd Apr 12 '17 at 13:59
You need to supply a SQLAlchemy engine to `to_sql`... – mechanical_meat Apr 12 '17 at 14:01
Have a look at: http://stackoverflow.com/questions/25661754/get-data-from-pandas-into-a-sql-server-with-pyodbc – mechanical_meat Apr 12 '17 at 14:02

Load a dataframe to SQL Server from pandas?

0 Answers0