Introduction

In this article, we will see how to connect SQL Server with Python using the pyodbc library. If you are a SQL DBA, we strongly recommend running Python scripts in SSMS.

However, we have some Python developers who want to work with Python directly instead of working with SSMS and enabling scripts. We will cover the following topics:

1. First, we will see how to connect SQL Server with Python and get data using pyodbc.
2. Secondly, we will get data from SQL Server using a stored procedure with Python.
3. Thirdly, we will insert data to SQL Server using a stored procedure in Python.
4. Finally, we will insert data from a CSV file into SQL Server.

Requirements

1. First, you will need SQL Server database engine installed.
2. Secondly, a Python Code Editor. In my case, I will use the Visual Studio Code, but you can use any software of your preference.
3. Thirdly, the Adventureworks2019 database. You can use a different database, but you will need to modify the code.
4. Finally, and optionally, I recommend installing SQL Server Management Studio to verify the data.

How to connect SQL Server with Python and get data using pyodbc

The following code will connect to SQL Server and get information from the person.person table.

import pyodbc
#Connection information
# Your SQL Server instance
sqlServerName = '.'
#Your database
databaseName = 'AdventureWorks2019'
# Use Windows authentication
trusted_connection = 'yes'
# Connection string information
connenction_string = (
f"DRIVER={{SQL Server}};"
f"SERVER={sqlServerName};"
f"DATABASE={databaseName};"
f"Trusted_Connection={trusted_connection}"
)
try:
   # Create a connection
   connection = pyodbc.connect(connenction_string )
   cursor = connection.cursor()
   # Run the query to the Person.Person table
   query = 'SELECT * FROM Person.Person'
   cursor.execute(query)
   # print the results of the row
   rows = cursor.fetchall()
   for row in rows:
      print(row)
except pyodbc.Error as ex:
      print("An error occurred in SQL Server:", ex)
# Close the connection
finally:

First, we use the import pyodbc library. If you do not have this library installed in the command line, you need to install the pyodbc for Python:

pip install pyodbc

The next lines will connect to the local SQL Server, the Adventureworks2019 database using Windows Authentication:

#Connection information
# Your SQL Server instance
sqlServerName = '.'
#Your database
databaseName = 'AdventureWorks2019'
# Use Windows authentication
trusted_connection = 'yes'
# Connection string information
connenction_string = (
f"DRIVER={{SQL Server}};"
f"SERVER={sqlServerName};"
f"DATABASE={databaseName};"

Next, we use the connection and run the query. The query will get the data from the Person.Person table of the Adventureworks2019 database.

# Create a connection
connection = pyodbc.connect(connenction_string )
cursor = connection.cursor()
# Run the query to the Person.Person table
query = 'SELECT * FROM Person.Person'
cursor.execute(query)

Finally, we print the results and close the connection.

except pyodbc.Error as ex:
   print("An error occurred in SQL Server:", ex)
# Close the connection
finally:
   if 'connection' in locals():
       connection.close()

Get data from SQL Server using a stored procedure with Python

Now, we will create the code to connect to SQL Server invoking a SQL Server Stored Procedure in Python.

First, we will create the stored procedure code to get the information from the person.person table.

CREATE PROCEDURE [dbo].[GetPersons]
AS
SELECT * FROM Person.Person;

Secondly, we will invoke the stored procedure created using Python:

import pyodbc
# Connection information
sqlServerName = '.' # Your SQL Server instance
databaseName = 'AdventureWorks2019'
trusted_connection = 'yes' # Use Windows authentication
# Connection string information
connection_string = (
f"DRIVER={{SQL Server}};"
f"SERVER={sqlServerName};"
f"DATABASE={databaseName};"
f"Trusted_Connection={trusted_connection}"
)
try:
# Create a connection
connection = pyodbc.connect(connection_string)
cursor = connection.cursor()
# Execute the stored procedure
stored_procedure = 'GetPersons'
# Call the stored procedure
cursor.execute("{CALL " + stored_procedure + "}")
# Fetch and print the results
rows = cursor.fetchall()
for row in rows:
print(row)
except pyodbc.Error as ex:
print("An error occurred in SQL Server:", ex)
finally:
# Close the connection
if 'connection' in locals():
connection.close()
Most of the lines are similar that the first example running a query.

Most of the lines are similar to the first example running a query.

Here are the lines of code that are different:

stored_procedure = 'GetPersons'
# Call the stored procedure
cursor.execute("{CALL " + stored_procedure + "}")

We are invoking the stored procedure GetPersons.

Insert data to SQL Server using a stored procedure in Python

In the next example, we will create a stored procedure that inserts data into a SQL Server database.

First, we will create a stored procedure with parameters to insert data into the sales.currency table:

CREATE PROCEDURE [dbo].[InsertCurrency]
  @CurrencyCode nchar(3),
  @Name dbo.Name
AS
INSERT INTO [Sales].[Currency] ([CurrencyCode], [Name], [ModifiedDate])
 VALUES (@CurrencyCode, @Name, GETDATE());

Secondly, we will create the Python code to invoke the stored procedure:

import pyodbc
# Connection information
sqlServerName = '.' # Your SQL Server instance
databaseName = 'AdventureWorks2019'
trusted_connection = 'yes' # Use Windows authentication
# Connection string information
connection_string = (
 f"DRIVER={{SQL Server}};"
 f"SERVER={sqlServerName};"
 f"DATABASE={databaseName};"
 f"Trusted_Connection={trusted_connection}"
 )
try:
    # Create a connection
    connection = pyodbc.connect(connection_string)
    cursor = connection.cursor()
    # Parameters used by the stored procedure
    currency_code = 'MEU'
    name = 'Sql Server Central Euros'
    # Call the stored procedure
    stored_procedure = 'InsertCurrency'
    cursor.execute("{CALL " + stored_procedure + " (?, ?)}", (currency_code, name))
    # Commit the transaction
    connection.commit()
    print("Stored procedure executed successfully!")
except pyodbc.Error as ex:
    print("An error occurred in SQL Server:", ex)
    connection.rollback()
finally:
    # Close the connection
    if 'connection' in locals():
        connection.close()

If everything is OK, the Python code will insert data into the sales.currency table.

The following lines are the most important ones:

# Parameters used by the stored procedure
currency_code = 'MEU'
name = 'Sql Server Central Euros'
# Call the stored procedure
stored_procedure = 'InsertCurrency'
cursor.execute("{CALL " + stored_procedure + " (?, ?)}", (currency_code, name))

Basically, we will insert the currency code and a name for the currency. Then we invoke the stored procedure and send values to 2 parameters.

If everything is OK, we will see the new data inserted:

Insert data from a CSV file into SQL Server

Finally, we have this CSV file named currencies.csv with this data:

WIZ, Wizarding Galleon
STK, Starkmark
FOR, Jedi Credit
AVC, Avenger Coin
NRN, Narnian Silver Star
PTW, Galleon of Wizardry
MTR, Neo Coin
WAK, Wakandan Vibranium Token

We want to insert the data from the csv file into the sales.currency table. The following Python code will do that:

import pyodbc
import csv
# Connection information
sqlServerName = '.' # Your SQL Server name
databaseName = 'AdventureWorks2019'
trusted_connection = 'yes' # Use Windows authentication
# Connection string information
connection_string = (
f"DRIVER={{SQL Server}};"
f"SERVER={sqlServerName};"
f"DATABASE={databaseName};"
f"Trusted_Connection={trusted_connection}"
)
try:
    # Create a connection
    connection = pyodbc.connect(connection_string)
    cursor = connection.cursor()
    # Read currencies from CSV file and insert into the database
    with open('c:\data\currencies.csv', 'r') as csv_file:
        csv_reader = csv.reader(csv_file)
        next(csv_reader) # Skip the header row if it exists
        for row in csv_reader:
             currency_code = row[0]
             name = row[1]
        stored_procedure = 'InsertCurrency'
        cursor.execute("{CALL " + stored_procedure + " (?, ?)}", (currency_code, name))
        connection.commit()
        print(f"Inserted: {currency_code}, {name}")
        print("All currencies inserted successfully!")
except pyodbc.Error as ex:
    print("An error occurred in SQL Server:", ex)
    connection.rollback()
finally:
    # Close the connection
    if 'connection' in locals():
        connection.close()

First, we read the data from the csv file.

# Read currencies from CSV file and insert into the database
with open('c:\data\currencies.csv', 'r') as csv_file:
csv_reader = csv.reader(csv_file)
next(csv_reader)

Secondly, we read the values in rows.

for row in csv_reader:
currency_code = row[0]
name = row[1]

Finally, we execute the stored procedure and insert the values.

stored_procedure = 'InsertCurrency'
cursor.execute("{CALL " + stored_procedure + " (?, ?)}", (currency_code, name))

Conclusion

In this article, we learned how to connect to SQL Server using the pyodbc. We learned how to run a query, how to run stored procedures with parameters, and finally, we imported data from a csv file into SQL Server using Python.

Images

Some images were generated in Bing Image Creator.