How to Execute a SQL Query In Python With Variables

We touched on using parameters in recent post so I wanted to expand on that and show an example for including variables in the SQL statements. If your not familiar with setting up a SQL connection in python, see our post called How to Connect SQL Server in Python.

Or if your looking for MySQL or MariaDB Try here.

In many cases you will need to query the database to create a List, Tuple or Dictionary and use those results to execute another statement or loop through values.

Python Variables

Here is a quick summary of variable types and assignment. You will notice that the variable names are all lowercase and the boolean values are case sensitive too. You cannot use all caps like TRUE or FALSE for the boolean values. This is a simple assignment and the syntax is similar to most languages.

## String: 
	app_name = "Some value for string"
	app_name = 'Same using single quotes'
## Number: 
	num_tests = 214
## Decimal: 
	decimal_here = 23.18
## Boolean (1 or 0): 
	is_enabled = True
	is_enabled = False

Python SQL Server Connection

For this example we will use the previous connection code and get the parameters from variables. This makes the connection settings dynamic and the code reusable even in the same application. Create a new connection by simply changing the variables.

This is our starting code where the parameters are already assigned to the connection:

import pyodbc

conn_str = ("Driver={SQL Server};"
            "Server=mysqlserver\sqlinst1;"
            "Database=DBName;"
            "UID=MyUser;"
            "PWD=Pass123;")
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT TOP(1000) * FROM sometable")

for row in cursor:
    print(row)

In this example we are showing the same connection with the parameters placed in variables instead. We will leave the Driver value for SQL Server in the “conn_str” syntax since it is unlikely this will be changed often.

We now assign the variables and add them to our “conn” connection object as parameters to the connection.

import pyodbc

server= 'mysqlserver\sqlinst1'
db= 'DBName'
user= 'MyUser'
password= 'Pass123'

conn = pyodbc.connect("driver=SQL Server;server=%s;database=%s;uid=%s;pwd=%s" % ( server, db, user, password ) )
cursor = conn.cursor()
cursor.execute("SELECT * FROM SomeTable")

for row in cursor:
    print(row)

Notice the percent “%” sign syntax and that the code is using a Tuple so the parameters need to be in the correct order.

%s – generic string or object
%d – decimal integer
%f – float
%g – generic number

This was pretty straight forward example, what about a larger environment where we have Active Directory service accounts?

Windows Authentication

If your working in a domain and you want to use the logged in users domain credentials you can set the “Trusted Connection” parameter. This is what the code would look like:

import pyodbc

server= 'mysqlserver\sqlinst1'
db= 'DBName'

conn = pyodbc.connect("driver=SQL Server;Trusted_Connection=Yes;server=%s;database=%s" % ( server, db) )
cursor = conn.cursor()
cursor.execute("SELECT * FROM SomeTable")

for row in cursor:
    print(row)

The user that is executing the Python code will be used automatically to authenticate to the database.

A more dynamic solution here is to assign the “The Trusted Connection” to a variable (db tales com) so it can be an option from within the application. You can switch from the logged on user to a defined user and password through a settings page.

Python Environment Variables

Often the environment variables are used instead of a static assignment. These are variables that exist at the OS level in the current environment you are running. To see these Variables you can run the following:

import os

for i, j in os.environ.items():
    print(i, j)

I have used many of these in Administrative type applications as they needed the machine name or “COMPUTERNAME”, or the “HOMEPATH”, “JAVA_HOME”, “PYTHONPATH”, “USERDOMAIN” or “USERNAME”. The os module makes this available so I don’t need to access the WMI through another method.

import os

user = os.environ['USERNAME']
computer = os.environ['COMPUTERNAME']
domain = os.environ['USERDOMAIN']

print(domain,user,computer)

This can be useful for setting defaults in your SQL database application such as the Domain and Login information for this post purposes.