Python with SQL Databases

In this post, I'll go over everything you need to know about connecting Python and SQL Server.

You'll learn how to directly pull data from relational databases into your machine learning pipelines, save data from your Python application in your own database, and any other use case you can think of.

We will Cover

Connecting to SQL Server in Python
Creating Tables and Table Relationships
Populating Tables with Data
Reading Data
Updating Records
Deleting Records
Creating re-usable functions to do all of this for us in the future

Here i'm using VSCode for practoce, you can use any IDE that you comfortable. I will attach the jupiter source file in the summary section.

If you are unfamiliar with SQL and the ideas underlying relational databases, I would recommend the courses listed below.

https://www.w3schools.com/sql/

https://www.w3schools.com/mysql/mysql_rdbms.asp

Why Python with SQL

Python has numerous advantages for Data Analysts and Data Scientists. It is an extremely valuable tool for any Data Analyst because to a large number of open-source libraries. Python has a vast ecosystem of libraries and frameworks for working with databases. Libraries like pyodbc, sqlite3, SQLAlchemy, and Django ORM provide different levels of abstraction for interacting with SQL databases, allowing you to choose the right tool for your project.

Python is cross-platform and can run on various operating systems. This means you can use Python to work with SQL databases on Windows, macOS, Linux, and other platforms, making it versatile for different environments.

Python can easily integrate with other technologies and data sources. You can use Python to fetch data from databases, process it, and then use it in other parts of your application or integrate it with web services, APIs, and more.

data science and data analysis libraries, such as NumPy, Pandas, and Matplotlib, make it a powerful choice for working with SQL databases in the context of data analysis, reporting, and visualization.

Python has a large and active community of developers. You can find abundant documentation, tutorials, and community support when working with Python and SQL databases. This community-driven approach ensures that you can quickly find solutions to common problems.

In this blog, I used SQLServer as a database; however, if you practise with SQLite or MySQL, some syntax may differ depending on the libraries that you use..

Requirments and Installations

Prerequisite

VSCode/Anaconda/Jupyter Notebook. or any equivalentIDE
SQLServer Express
SQL Server Management studio or equivalent

Install Python Libraries

MySQL : pip install mysql-connector-python
MSQLServer : pip install pyodbc

Create new Database in SQL Server

Login to SqlServer Managment Studio (SSMS)
Press right mouse key over database, and select Create database
Database name : testdb

In the below code snippets, i have written few reusable functions

Connecting to SQL Server Database

# Define the connection parameters
server = 'LAPTOP-I0B5PPTC\SQLEXPRESS'
database = 'testdb'
username = 'sa'
password = 'password***'

# Create a connection string
connection_string = f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'

# Establish a connection to the database
def establish_Connetion_Database(connectionstring):
    connection=None
    try:
        connection = pyodbc.connect(connection_string)
        cursor = connection.cursor()        
    except pyodbc.Error as e:
        print(f"Database error: {e}")
    return connection

Execute Query

def execute_query(connection, query):
    cursor=connection.cursor()
    try:
        cursor.execute(query)
    except pyodbc.Error as e:
        print(f"Error [execute_query] : {e}")
    return cursor

Close Open Connections

# Close the cursor and connection
def close_open_connection(curson,connection):
    if cursor:
        cursor.close()
        print("Cursor closed")
    if connection:
        connection.close()
        print("connection closed")

Check Given table exists in the database

# Function to check if a table exists
def table_exists(connection, table_name):
    cursor = connection.cursor()
    try:
        cursor.execute(f"SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{table_name}'")
        return cursor.fetchone() is not None
    except pyodbc.Error as e:
        print(f"Error checking table existence: {e}")
    finally:
        cursor.close()

Get all the records from student table., and print in the terminal window

#! print the data
connection=establish_Connetion_Database(connectionstring=connection_string)
cursor=execute_query(connection=connection,query='select * from student')
for row in cursor:
    print(row)

# Close the connections
close_open_connection(curson=cursor,connection=connection)

If the given table does not exist in the database, it should be created.

Please note, cursor.commit() command is necessary here in order to apply changes in the database

#! check table exists ?
connection=establish_Connetion_Database(connectionstring=connection_string)
cursor=execute_query(connection=connection,query="SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'marks'")
if cursor.fetchone() is None :
    #! Create a new table
    # Define the SQL query to create a new table
    create_table_query = '''
    CREATE TABLE marks (
        mark_id INT identity(1,1),
        subject VARCHAR(50),
        faculty VARCHAR(50)
    )
    '''
    cursor=execute_query(connection=connection,query=create_table_query)
    cursor.commit()
    print(f"Marks table Created")
else:
    print(f"Marks table is alreay exists")

Insert single row into the table

#! Insert Recotds to Table
connection=establish_Connetion_Database(connectionstring=connection_string)
cursor=execute_query(connection=connection,query="insert into marks(subject,faculty)values('English','john doe')")
cursor.commit()

Bulk inserts

#! bulk insert
bulk_insert_fields='INSERT INTO marks(subject,faculty)values(?,?)'
bulk_insert_values=[
    ('Name 1', 'Value 1'),
    ('Name 2', 'Value 2'),
    ('Name 3', 'Value 3')]
cursor=connection.cursor()
cursor.executemany(bulk_insert_fields,bulk_insert_values)
cursor.commit()

Get Row IDENTITY

returns the last IDENTITY value inserted into an IDENTITY column in the same scope.

# Get the last identity value
print("--- IDENTITY  ---")
cursor.execute('SELECT SCOPE_IDENTITY()')
last_identity = cursor.fetchone()[0]
print(f'Last Identity Value: {last_identity}')

# In MySQL, we can use cursor.lastrowid

Read/Fetch Records from the database

#! Fetch all records
print("--- cursor.fetchall() ---")
cursor.execute("select * from marks")
results=cursor.fetchall()
for row in results:
    print(row)

#! Fetch one record
print("--- cursor.fetchone() ---")
cursor.execute("select * from marks")
results=cursor.fetchone()
print(results)

#! Close the connections    
close_open_connection(curson=cursor,connection=connection)