Let's say you scraped something from the web, or you have a dataset that you modeled in Python and now you want to store it somewhere. What? Into SQL Server you say? Sure!
Here are the steps on how to insert data from Python into SQL Server. If you want to know how to work the other way around (from SQL server to Python (Pandas DataFrame) , check this post.
The steps are as follows:
Connect to SQL Server
Creating a (fictional) Pandas DataFrame (df)
Importing data from the df into a table in SQL Server
In this example, I take an existing table from SQL Server, load it into a DataFrame and put it right back were it came from. Yes, this means duplicate records (over and over again), but you get the point.
import pypyodbc
#Connect to SQL server
def open_connection():
try:
global con
print('Trying to connect to SQL Server....')
con = pypyodbc.connect('Driver={SQL Server};'
'Server=servername;' #enter your servername here
'Database=databasename;' #enter your databasename here
'Trusted_Connection=yes;')
print('Connection made!')
except:
print('Could not connect!')
You probably already know into what table you are going to import your data. For this example, we are going to take data from that given table. I use a single string as an example ('SELECT * FROM DATABASE.TABLE') but it is also possible to convert a .sql file into a string. That is covered here and here.
open_connection()
query = """
select * from database.table
"""
df = pd.read_sql_query(query,con)
#optional: describe the df you just filled to check the #rows
df.describe()
import pypyodbc
import pandas as pd
open_connection()
cursor = con.cursor()
#import data into SQL Server
for index, row in df.iterrows():
cursor.execute("INSERT INTO database.table([column1],[column2]) values (?, ?)", [row['column1'], row['column2']])
con.commit()
cursor.close()
#optional: describe the table you just filled to check if the #rows are updated
print(pd.read_sql_query(query,con).describe())
#make sure to close the connection: see link below
Make sure to close the connection.
How to load data from Python into SQL Server? How to connect Python to SQL? How to establish a connection between Python and SQL Server? How to import data from Python to SQL Server? How to upload data from a Pandas DataFrame into SQL Server? How to import data into SQL Server from Python?