How to insert the data to SQL Server using Python pyodbc?

Today lesson is how we will insert the data to MSSQL Server. We are using python (Latest: Python 3.8.2), pyodbc plugin to achieve this. If you are not sure how to install the Python and plugins, check my previous post.

1. Download and install pyodbc library

$ pip install pyodbc

2. Insert one record to the test table:

# importing the pyodbc library
import pyodbc
# connect to MSSQL DB
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};
                            SERVER=SQLEXPRESS;
                            DATABASE=TEST;
                            UID=sa;
                            PWD=sa;') 
cursor = conn.cursor()
# insert data to table
cursor.execute("INSERT INTO dbo.test([Column1],
                        [Column2],[Column3]) 
                         values (?,?,?)", 'test1', 
                                          'test2', 
                                          'test3') 
cursor.commit()
print(cursor.rowcount, "record(s) affected")
# close DB connection
cursor.close()
conn.close()

That’s it. I hope this will help. ?
Photo: https://www.brandeps.com