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:

1. This is a mission critical database and some of the DataFrames must be written as delete queries

2. 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

3. 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)

Answer 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")