Create table and Insert to SQL Server using Python

Question

I have a huge table (147 columns) and I would like to know if it is possible to create the table in SQL server from my pandas dataframe so I don't have to CREATE TABLE for 147 columns.

I am trying to follow what is answered in most of related questions:

params = urllib.parse.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};SERVER=DESKTOP-LFOSSEF;DATABASE=test;UID=xxxx;PWD=xxx")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
connection = engine.raw_connection()
df.to_sql("table_name", connection,index=False)

The user and password work because that's what I am using to sign in into sqlserver.

When I run this, I get:

Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

If I remove the connection = engine.raw_connection() and use engine directly, I get:

AttributeError: 'Engine' object has no attribute 'cursor'

Any idea what is wrong? Do I need to create an empty table first? I've been troubleshooting for hours and can't find any reason why this isnt working.

Passing engine as the second parameter to to_sql() should work. What versions of SQLAlchemy and pandas are you using? — Gord Thompson
– Gord Thompson, Commented Jan 23, 2021 at 14:00
sqlalchemy.engine.base.Engine, don't know how it get to sqlalchemy though — vftw
– vftw, Commented Jan 23, 2021 at 14:22
That is the correct type. This code works fine for me. Perhaps your SQLAlchemy install is broken. You could try pip uninstall sqlalchemy followed by pip install sqlalchemy and see if that helps. — Gord Thompson
– Gord Thompson, Commented Jan 23, 2021 at 14:56

ASH · Accepted Answer · 2021-01-23 23:41:45Z

1

Do it like this.

import pyodbc

engine = "mssql+pyodbc://Your_Server_Name/Your_DB_Name?driver=SQL Server Native Client 11.0?trusted_connection=yes"

df.to_sql(x, engine, if_exists='append', index=True)

df = name of your dataframe & x = name of your table in SQL Server

answered Jan 23, 2021 at 23:41

ASH

20.5k28 gold badges115 silver badges246 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Firke Over a year ago

There being two question marks caused this to fail for me. Change the second one to an & and it worked!

Collectives™ on Stack Overflow

Create table and Insert to SQL Server using Python

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related