Python is a powerful programming language that can be used to interact with various databases, including SQL Server. In this article, we will explore how to connect SQL Server with Python using the pyodbc library.

Requirements

Before we begin, make sure you have the following:

• SQL Server database engine installed
• A Python Code Editor (e.g., Visual Studio Code)
• The Adventureworks2019 database (or any other database of your choice)
• Optionally, SQL Server Management Studio to verify the data

Connecting SQL Server with Python and retrieving data

To connect SQL Server with Python, we need to use the pyodbc library. If you don’t have it installed, you can install it using the following command:

pip install pyodbc

Once installed, we can establish a connection to SQL Server and retrieve data using the following code:

import pyodbc

# Connection information
sqlServerName = '.'
databaseName = 'AdventureWorks2019'
trusted_connection = 'yes'

# 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()

    # Run the query to the Person.Person table
    query = 'SELECT * FROM Person.Person'
    cursor.execute(query)

    # 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()

This code establishes a connection to the SQL Server instance and the Adventureworks2019 database. It then executes a query to retrieve data from the Person.Person table and prints the results.

Executing a stored procedure in SQL Server using Python

We can also execute stored procedures in SQL Server using Python. Here’s an example:

import pyodbc

# Connection information
sqlServerName = '.'
databaseName = 'AdventureWorks2019'
trusted_connection = 'yes'

# 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'
    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()

In this example, we execute the stored procedure “GetPersons” in SQL Server and retrieve the results using Python.

Inserting data into SQL Server using a stored procedure in Python

We can also insert data into SQL Server using a stored procedure in Python. Here’s an example:

import pyodbc

# Connection information
sqlServerName = '.'
databaseName = 'AdventureWorks2019'
trusted_connection = 'yes'

# 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()

In this example, we insert data into the sales.currency table in SQL Server using the stored procedure “InsertCurrency”. We pass the currency code and name as parameters to the stored procedure.

Inserting data from a CSV file into SQL Server

We can also insert data from a CSV file into SQL Server using Python. Here’s an example:

import pyodbc
import csv

# Connection information
sqlServerName = '.'
databaseName = 'AdventureWorks2019'
trusted_connection = 'yes'

# 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()

In this example, we read data from a CSV file named “currencies.csv” and insert it into the sales.currency table in SQL Server using the stored procedure “InsertCurrency”. Each row in the CSV file represents a currency code and name.

Conclusion

In this article, we have explored how to connect SQL Server with Python using the pyodbc library. We have learned how to retrieve data, execute stored procedures, and insert data into SQL Server using Python. With these techniques, you can leverage the power of Python to interact with your SQL Server databases.