Writing to a SQL Server database from Pandas using PYODBC

Question

I've reached the writing to a SQL Server database part of my data journey, I hope someone is able to help.

I've been able to successfully connect to a remote Microsoft SQL Server database using PYODBC this allows me to pass in SQL queries into dataframes and to create reports.

I now would want to automate the "select import" manual method I've had a read of many blogs but I'm none the wiser to understanding the how behind it all.

import pandas as pd
import pyodbc

SERVER = r'Remote SQL Server'
database = 'mydB'
username = 'datanovice'
password = 'datanovice'
cnxn = pyodbc.connect('Driver={SQL 
Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ 
password)
cursor = cnxn.cursor()

I'm able to read queries easily using this and pass them into dataframes.

what's the best way to write into my MS SQL dB? noting that it's not local I'm happy to pass this into SQL Alchemy but I wasn't sure of the correct syntax.

Things to consider:

This is a mission critical database and some of the DataFrames must be written as delete queries
If this is an unsafe method and if I need to go back and study more to understand proper database methodology I'm very happy to do so
I'm not looking for someone to write or provide the code for me, but rather point me in the right direction

I envisage this to be something like.. but I'm not sure how I specify the correct table:

df.to_sql('my_df', con, chunksize=1000)

I've read through the above but I'm unclear as to how to create an engine from my PYODBC for SQL alchemy are you able to assist? I could then use the following df1.to_sql('users', con=engine, if_exists='replace', ... index_label='id') p.s love the OPM picture! — Umar.H
– Umar.H, Commented Nov 6, 2018 at 15:17

Gord Thompson · Accepted Answer · 2018-11-06 16:46:49Z

1

As you've seen from the pandas documentation you need to pass a SQLAlchemy engine object as the second argument to the to_sql method. Then you can use something like

df.to_sql("table_name", engine, if_exists="replace")

The SQLAlchemy documentation shows how to create the engine object. If you use an ODBC DSN then the statement will look something like this:

from sqlalchemy import create_engine
# ...
engine = create_engine("mssql+pyodbc://scott:tiger@some_dsn")

answered Nov 6, 2018 at 16:46

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

2 Comments

Umar.H Over a year ago

that makes sense, SQLAlchemy Documentation here I come!

Umar.H Over a year ago

Thanks buddy, figured out the connection, had to use urrlib.parse but I was able to do what illustrated above. Cheers Gord my man, owe you a coffee or two.

Collectives™ on Stack Overflow

Writing to a SQL Server database from Pandas using PYODBC

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related