Free SQL Help for Devs, DBAs, and the Curious

The power of Python cannot be overstated, especially when it comes to interacting with databases. Python's pyodbc module simplifies the process of connecting to ODBC databases, such as SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL. In this post, we'll focus on executing parameterized DB2 queries from Python, a crucial requirement for preventing SQL injection attacks and ensuring the robustness of your data-driven applications.

Firstly, let's establish a connection to a DB2 database. Pyodbc enables this connection via a connection string. The connection string includes the driver, server, database, user ID, and password. You must install the IBM DB2 ODBC driver on your system to facilitate this connection.

import pyodbc

conn = pyodbc.connect(
    'DRIVER={IBM DB2 ODBC DRIVER};'
    'DATABASE=myDatabase;'
    'HOSTNAME=myHost;'
    'PORT=50000;'
    'PROTOCOL=TCPIP;'
    'UID=myUsername;'
    'PWD=myPassword;'
)

Once connected, you can execute queries through the connection's cursor. In the following example, we'll execute a simple SELECT query with a parameter. Notice the use of '?' as a placeholder for the parameter. This is essential for preventing SQL injection attacks.

cursor = conn.cursor()
param = 'John Doe'
cursor.execute("SELECT * FROM Users WHERE name = ?", param)

After the query execution, you can fetch the results using fetchone(), fetchall(), or fetchmany() methods. Remember to always close the connection once you're done with it.

row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()
conn.close()

Notably, the same principles of constructing queries and fetching results can be applied to other SQL databases such as SQL Server and MySQL. The only difference lies in the connection string. Here's an example of a connection string for SQL Server 2017 and MySQL 5.7 respectively.

# SQL Server 2017
conn = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=myServer;'
    'DATABASE=myDatabase;'
    'UID=myUsername;'
    'PWD=myPassword;'
)

# MySQL 5.7
conn = pyodbc.connect(
    'DRIVER={MySQL ODBC 5.7 Unicode Driver};'
    'SERVER=myServer;'
    'DATABASE=myDatabase;'
    'USER=myUsername;'
    'PASSWORD=myPassword;'
    'OPTION=3;'
)

In summary, Python's pyodbc module makes it easy to interact with various databases, allowing developers to build robust, data-driven applications. The simplicity of executing parameterized queries and fetching results gives Python an edge in database operations. Always remember to use parameterized queries to prevent SQL injection attacks, a common security vulnerability in database applications.