How to access SQL server with Python?

Prerequisites:

1. Ensure you have python 3 and above installed in your environment
2. Ensure you have SQL server Management Studio installed in your environment

In order to connect to SQL server, we’re going to install python library pyodbc. To install this library please run command below:

pip install pyodbc

After that, complete library installation, we’re going to write a simple wrapper with 2 functions to interact with SQL server:

1. Fetch data
2. Execute commands with no dataset return like insert / update / delete / execute stored procedures / create & drop table, etc

Please refer to wrapper class below (named as connection.py):

import pyodbc

class connection:
    def __init__(self, conn_string, query, params):
        # refer to documentation
        # https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows
        driver = r'DRIVER={ODBC Driver 17 for SQL Server};'
        self.conn =pyodbc.connect( driver + conn_string)
        self.query = query
        self.params = params

    # function to fetch data
    def fetch_query(self):
        c = self.conn.cursor()
        c.execute(self.query, self.params)
        results = c.fetchall()
        self.conn.close()
        return results

    # function without data return
    def exec_query(self):
        c = self.conn.cursor()
        c.execute(self.query, self.params)
        self.conn.commit()
        self.conn.close()

    def close(self):
        self.conn.close()

Let’s write some python code to access SQL server database with our wrapper class above.

from connection import connection

if __name__ == "__main__":
    conn_string = r'SERVER=localhost\SQLEXPRESS;DATABASE=demo;Trusted_Connection=yes;'

    # create a table
    query1 = """
        create table city (
            [id] int,
            [name] nvarchar(50),
            [status] bit
        )
    """
    conn1 = connection(conn_string=conn_string, query=query1, params=[])
    conn1.exec_query()


    # insert some data
    query2 = """
        insert into city(id, name, status) values (1, ?, 1)
        insert into city(id, name, status) values (2, ?, 1)
        insert into city(id, name, status) values (3, ?, 1)
        insert into city(id, name, status) values (4, ?, 1)
    """
    params2 = ['Kuching', 'Sibu', 'Miri', 'Kuala Lumpur']
    conn2 = connection(conn_string=conn_string, query= query2, params=params2)
    conn2.exec_query()


    # update some data
    query3 = """
        update city set status = 0  where id = ?
    """
    params3 = [4] # set Kuala Lumpur as inactive
    conn3 = connection(conn_string=conn_string, query=query3, params=params3)
    conn3.exec_query()

    # retrieve data from city table
    query4 = """
        select * from city where status = ?
    """
    params4 = [1]
    conn4 = connection(conn_string=conn_string, query=query4, params=params4)
    cities = conn4.fetch_query()
    # loop through every row and print data
    for city in cities:
        print(city)

    # drop table
    query5 = """
        drop table city
    """
    conn5 = connection(conn_string=conn_string, query=query5, params=[])
    conn5.exec_query()

Finally, you should get results printed in console as below:

(1, 'Kuching', True)
(2, 'Sibu', True)
(3, 'Miri', True)

Feel free to checkout my Github repo for further details.
Thanks for reading. Happy Coding 🙂