WaSQL Wired
Posts
Querying Databases with Python

Querying Databases with Python

SQLite, MySQL, MSSQL, PostgreSQL, Oracle, SAP HANA, Snowflake, MS Access, Firebird

Steven Lloyd
March 07, 2025

Python is a high-level, versatile programming language that has gained immense popularity due to its simplicity, readability, and extensive libraries. Python's versatility allows it to be used in a wide range of fields, including web development, data science, artificial intelligence, machine learning, automation, and scientific computing.

It is also a great language for querying databases. This article will explore how to query a variety of databases using Python. As you go through these examples notice that the only thing that really changes is the module you include and the connection. The rest is the same.

SQLite - SQLite is a lightweight, serverless, self-contained relational database that is widely used for its simplicity, portability, and efficiency. Unlike traditional databases, SQLite does not require a separate server process and stores the entire database in a single file, making it ideal for embedded systems, mobile applications, and small-scale projects.

#! python
"""
Installation - should not need to install since sqlite3 is part of pythons standard library
    python3 -m pip install sqlite3
References
	https://www.sqlitetutorial.net/sqlite-python/
"""

#import needed libraries
import sqlite3 
import json

#create a function to convert objects to strings
def convertStr(o):
    return "{}".format(o)

#establish a connection to the sqlite database file
conn = sqlite3.connect('/var/tmp/example.db')

#get a cursor to the connection above. 
#	Cursors are used to execute SQL commands and fetch results from the database 
cursor = conn.cursor() 

#execute the query
cursor.execute("SELECT name, age, color FROM people") 

#get column names - lowercase them for consistency
fields = [field_md[0].lower() for field_md in cursor.description]

#lets loop through the query results now stored in the cursor and build a list of records
recs = []
for rec in cursor.fetchall():
    # Convert rec to a dictionary
    rec_dict = dict(zip(fields, rec))
    # Serialize to JSON and back to handle date objects. Pass objects to convertStr function
    rec_dict = json.loads(json.dumps(rec_dict, default=convertStr))
    recs.append(rec_dict)

#close the connection to the database
conn.close()

#display the results
print(recs)

MySQL - MySQL is a popular open-source relational database known for its reliability, performance, and ease of use. It uses SQL to manage data and is a core component of the LAMP stack for web development. MySQL supports features like data security, scalability, and cross-platform compatibility, making it suitable for both small projects and large enterprise applications. Its open-source nature and active community ensure continuous improvement and widespread adoption.

#! python
"""
Installation
    python3 -m pip install mysql.connector-python
    If that fails, try upgrading pip:
        python3 -m pip install --upgrade pip
        then try again
    If it still fails try
        python -m pip install -U setuptools
        python -m pip install -U wheel
        then try again
References
    https://dev.mysql.com/doc/connector-python/en/connector-python-cext-reference.html
"""

#import needed libraries
import mysql.connector 
import json

#create a function to convert objects to strings
def convertStr(o):
    return "{}".format(o)

#establish a connection to the database
conn = mysql.connector.connect(user='user', password='password', host='localhost', database='example') 

#get a cursor to the connection above. 
#	Cursors are used to execute SQL commands and fetch results from the database 
cursor = conn.cursor() 

#execute the query
cursor.execute("SELECT name, age, color FROM people") 

#get column names - lowercase them for consistency
fields = [field_md[0].lower() for field_md in cursor.description]

#lets loop through the query results now stored in the cursor and build a list of records
recs = []
for rec in cursor.fetchall():
    # Convert rec to a dictionary
    rec_dict = dict(zip(fields, rec))
    # Serialize to JSON and back to handle date objects. Pass objects to convertStr function
    rec_dict = json.loads(json.dumps(rec_dict, default=convertStr))
    recs.append(rec_dict)

#close the connection to the database
conn.close()

#display the results
print(recs)

MSSQL - Microsoft SQL Server (MSSQL) is a relational database developed by Microsoft. It supports a wide range of data types, advanced querying, and robust transaction management, making it suitable for both small-scale applications and large enterprise systems

#! python
"""
Installation
    python3 -m pip install pymssql
References
    https://www.pymssql.org/
    https://docs.microsoft.com/en-us/sql/connect/python/pymssql/step-3-proof-of-concept-connecting-to-sql-using-pymssql?view=sql-server-ver15
"""

#import needed libraries
import pymssql 
import json

#create a function to convert objects to strings
def convertStr(o):
    return "{}".format(o)

#establish a connection to the database
conn = pymssql.connect(server='localhost', user='user', password='password', database='example') 

#get a cursor to the connection above. 
#	Cursors are used to execute SQL commands and fetch results from the database 
cursor = conn.cursor() 

#execute the query
cursor.execute("SELECT name, age, color FROM people") 

#get column names - lowercase them for consistency
fields = [field_md[0].lower() for field_md in cursor.description]

#lets loop through the query results now stored in the cursor and build a list of records
recs = []
for rec in cursor.fetchall():
    # Convert rec to a dictionary
    rec_dict = dict(zip(fields, rec))
    # Serialize to JSON and back to handle date objects. Pass objects to convertStr function
    rec_dict = json.loads(json.dumps(rec_dict, default=convertStr))
    recs.append(rec_dict)

#close the connection to the database
conn.close()

#display the results
print(recs)

PostgreSQL - PostgreSQL is a powerful, open-source relational database known for its robustness, scalability, and compliance with SQL standards. It supports advanced data types, complex queries, and transactional integrity, making it suitable for a wide range of applications, from small projects to large enterprise systems. PostgreSQL also offers features like JSON support, full-text search, and extensibility through custom functions and extensions.

#! python
"""
Installation
    python3 -m pip install "psycopg[binary]"
        NOTE: you MUST put quotes around the name
References
    https://www.psycopg.org/psycopg3/docs/basic/usage.htm
"""

#import needed libraries
import psycopg 
import json

#create a function to convert objects to strings
def convertStr(o):
    return "{}".format(o)

# Connection parameters
params = {
    "dbname": "example",
    "user": "user",
    "password": "password",
    "host": "localhost",  # or your server's IP
    "port": 5432,         # default PostgreSQL port
}
#establish a connection to the database
conn = psycopg.connect(**params) 

#get a cursor to the connection above. 
#	Cursors are used to execute SQL commands and fetch results from the database 
cursor = conn.cursor() 

#execute the query
cursor.execute("SELECT name, age, color FROM people") 

#get column names - lowercase them for consistency
fields = [field_md[0].lower() for field_md in cursor.description]

#lets loop through the query results now stored in the cursor and build a list of records
recs = []
for rec in cursor.fetchall():
    # Convert rec to a dictionary
    rec_dict = dict(zip(fields, rec))
    # Serialize to JSON and back to handle date objects. Pass objects to convertStr function
    rec_dict = json.loads(json.dumps(rec_dict, default=convertStr))
    recs.append(rec_dict)

#close the connection to the database
conn.close()

#display the results
print(recs)

Oracle - Oracle Database is a highly scalable, reliable, and feature-rich relational database developed by Oracle Corporation. It is widely used for enterprise-level data storage, management, and processing, supporting complex workloads and large datasets. Known for its robust architecture, Oracle Database offers advanced features such as high availability, real application clusters (RAC), partitioning, and comprehensive security measures.

#! python
"""
Installation
    python3 -m pip install cx_Oracle
Note
    cx_Oracle requires Oracle Client libraries to be installed
    https://www.oracle.com/database/technologies/instant-client/downloads.html
        Select the version that matches your operating system and architecture (e.g., Windows x64, Linux x64, macOS).
        At a minimum, download the "Basic" or "Basic Light" package. 
References
    https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#connpool
    https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html
"""

#import needed libraries
import cx_Oracle 
import json

#create a function to convert objects to strings
def convertStr(o):
    return "{}".format(o)

#establish a connection to the database
conn = cx_Oracle.connect('user/password@localhost:1521/example') 

#get a cursor to the connection above. 
#	Cursors are used to execute SQL commands and fetch results from the database 
cursor = conn.cursor() 

#execute the query
cursor.execute("SELECT name, age, color FROM people") 

#get column names - lowercase them for consistency
fields = [field_md[0].lower() for field_md in cursor.description]

#lets loop through the query results now stored in the cursor and build a list of records
recs = []
for rec in cursor.fetchall():
    # Convert rec to a dictionary
    rec_dict = dict(zip(fields, rec))
    # Serialize to JSON and back to handle date objects. Pass objects to convertStr function
    rec_dict = json.loads(json.dumps(rec_dict, default=convertStr))
    recs.append(rec_dict)

#close the connection to the database
conn.close()

#display the results
print(recs)

SAP HANA - SAP HANA (High-Performance Analytic Appliance) is an in-memory, column-oriented relational database management system developed by SAP. It is designed to handle high volumes of data in real-time, enabling faster data processing and analytics.

#! python
"""
Installation
    python3 -m pip install hdbcli
References
    https://pypi.org/project/hdbcli/
    https://developers.sap.com/tutorials/hana-clients-python.html
"""

#import needed libraries
import hdbcli
from hdbcli import dbapi 
import json

#create a function to convert objects to strings
def convertStr(o):
    return "{}".format(o)

#establish a connection to the database
conn = dbapi.connect(address='localhost', port=39015, user='user', password='password') 

#get a cursor to the connection above. 
#	Cursors are used to execute SQL commands and fetch results from the database 
cursor = conn.cursor() 

#execute the query
cursor.execute("SELECT name, age, color FROM people") 

#get column names - lowercase them for consistency
fields = [field_md[0].lower() for field_md in cursor.description]

#lets loop through the query results now stored in the cursor and build a list of records
recs = []
for rec in cursor.fetchall():
    # Convert rec to a dictionary
    rec_dict = dict(zip(fields, rec))
    # Serialize to JSON and back to handle date objects. Pass objects to convertStr function
    rec_dict = json.loads(json.dumps(rec_dict, default=convertStr))
    recs.append(rec_dict)

#close the connection to the database
conn.close()

#display the results
print(recs)

Snowflake - Snowflake is a cloud-based data warehousing platform designed for modern data analytics and storage. Unlike traditional databases, Snowflake separates storage and compute resources, allowing users to scale each independently for cost efficiency and performance. Its architecture is built for the cloud, offering seamless integration with major cloud providers like AWS, Azure, and Google Cloud.

#! python
"""
Installation
    python3 -m pip install --upgrade snowflake-connector-python
       If it fails then go to https://visualstudio.microsoft.com/visual-cpp-build-tools/
           download build tools
           install c++ build tools
           reboot and try again
References
    https://pypi.org/project/snowflake-connector-python/
"""

#import needed libraries
import snowflake.connector 
import json

#create a function to convert objects to strings
def convertStr(o):
    return "{}".format(o)

#establish a connection to the database
conn = snowflake.connector.connect(user='user', password='password', account='example', warehouse='wh', database='example') 

#get a cursor to the connection above. 
#	Cursors are used to execute SQL commands and fetch results from the database 
cursor = conn.cursor() 

#execute the query
cursor.execute("SELECT name, age, color FROM people") 

#get column names - lowercase them for consistency
fields = [field_md[0].lower() for field_md in cursor.description]

#lets loop through the query results now stored in the cursor and build a list of records
recs = []
for rec in cursor.fetchall():
    # Convert rec to a dictionary
    rec_dict = dict(zip(fields, rec))
    # Serialize to JSON and back to handle date objects. Pass objects to convertStr function
    rec_dict = json.loads(json.dumps(rec_dict, default=convertStr))
    recs.append(rec_dict)

#close the connection to the database
conn.close()

#display the results
print(recs)

MS Access - Microsoft Access is a relational database developed by Microsoft. Like SQLite, Microsoft Access stores the entire database in a single file (with extensions like .accdb or .mdb), making it easy to manage and transport. However, unlike SQLite, Access is not a serverless database in the same sense. While it doesn’t require a separate server process for basic functionality, it relies on the Microsoft Access runtime or application to operate, which can limit its scalability and multi-user capabilities. Access is better suited for single-user or small multi-user environments, whereas SQLite is often used in embedded systems or lightweight applications. Both are file-based databases, but their use cases and architectures differ significantly.

#! python
"""
Installation
    python3 -m pip install pyodbc
References
    https://developers.sap.com/tutorials/msaccess-clients-python.html
    https://code.google.com/archive/p/pyodbc/wikis/FAQs.wiki
"""

#import needed libraries
import pyodbc 
import json

#create a function to convert objects to strings
def convertStr(o):
    return "{}".format(o)

#establish a connection to the database
conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=example.accdb;')

#get a cursor to the connection above. 
#	Cursors are used to execute SQL commands and fetch results from the database 
cursor = conn.cursor() 

#execute the query
cursor.execute("SELECT name, age, color FROM people") 

#get column names - lowercase them for consistency
fields = [field_md[0].lower() for field_md in cursor.description]

#lets loop through the query results now stored in the cursor and build a list of records
recs = []
for rec in cursor.fetchall():
    # Convert rec to a dictionary
    rec_dict = dict(zip(fields, rec))
    # Serialize to JSON and back to handle date objects. Pass objects to convertStr function
    rec_dict = json.loads(json.dumps(rec_dict, default=convertStr))
    recs.append(rec_dict)

#close the connection to the database
conn.close()

#display the results
print(recs)

Firebird - Firebird is an open-source, relational database known for its compact size, high performance, and robust feature set. It is based on the InterBase codebase and has been actively developed by a global community. It can operate in embedded mode (like SQLite) or in a client-server mode for multi-user environments. It is highly portable, running on various operating systems, including Windows, Linux, and macOS, and can operate in embedded, server, or hybrid modes.

#! python
"""
Installation
    python3 -m pip install fdb
References
    https://firebird-driver.readthedocs.io/en/latest/getting-started.html#quick-start-guide
    https://firebirdsql.org/file/documentation/html/en/firebirddocs/qsg3/firebird-3-quickstartguide.html

"""

#import needed libraries
import fdb 
import json

#create a function to convert objects to strings
def convertStr(o):
    return "{}".format(o)

#establish a connection to the database
conn = fdb.connect(dsn='localhost:example.fdb', user='user', password='password') 

#get a cursor to the connection above. 
#	Cursors are used to execute SQL commands and fetch results from the database 
cursor = conn.cursor() 

#execute the query
cursor.execute("SELECT name, age, color FROM people") 

#get column names - lowercase them for consistency
fields = [field_md[0].lower() for field_md in cursor.description]

#lets loop through the query results now stored in the cursor and build a list of records
recs = []
for rec in cursor.fetchall():
    # Convert rec to a dictionary
    rec_dict = dict(zip(fields, rec))
    # Serialize to JSON and back to handle date objects. Pass objects to convertStr function
    rec_dict = json.loads(json.dumps(rec_dict, default=convertStr))
    recs.append(rec_dict)

#close the connection to the database
conn.close()

#display the results
print(recs)

Thanks for reading! If you found this article enlightening then please subscribe AND SHARE! See you next week :)

——————————————————————————————————————

Sponsor: Now that your all charged up about Python perhaps you could use a way to charge batteries at home. I recently purchased this battery charger on Amazon and have been super impressed with how well it works. Check it out. https://www.amazon.com/dp/B08PBQF1FH?tag=fingerpointfo-20