Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Python has grown explosively in popularity in the past 5 years. Many new programmers are drawn to it because of its gentle learning curve relative to other programming languages. Experienced programmers are drawn to it because of its extensibility and power. However one of the major drivers of this mass adoption is the ease of which Python can work with databases. This Python programming tutorial will explore how to get started with using Python to communicate with SQL Express using Python 3.
Python and Database Programming
A Python beginner who wishes to integrate database functionality into any software project written in any language must have a basic understanding of at least two languages. The first is, of course, Python and the second being the specific structured query language (SQL) used by the database. While SQL is standardized, in practice it is not a universal language, but the implementations between various databases are close enough that moving from one database to another is not that much of a challenge once one has a comfortable level of database application development experience.
Another important consideration for databases is that they all require management software. These management tools can assist with setting up access to, and security privileges within, a database. They can also be used to debug database applications as they can enable a developer to do things like:
- Creation and management of the content of tables, views and other database objects. This includes the relationships between tables, as well as the configuring of integrity rules.
- Interaction with the database directly via SQL code entry.
- Debugging of SQL syntax.
- Undoing (to an extent) of damage caused by incorrectly coded SQL statements in the application.
Even if a developer chooses to use a noSQL-based database such as MongoDB, there will still be challenges in learning the database-specific coding syntax needed to make such a solution work. Of course, these are not deterrents, they are just technical factors that must be considered with any software development project.
Python and Database Drivers
Python, like any other programming language, cannot natively communicate with a given database. It requires extra modules to be added for a specific database server. From a best practice standpoint, it is best to use a database driver module that is specific to the database server chosen for the software project. Doing so ensures that Python can access all of the features of the database server, even if it comes at the added cost of using a specific programming syntax for the module. While some attempts have been made to create “universal” database driver modules that can connect to multiple database servers, these often come at the expense of losing access to certain features of a particular database server.
What is SQL Express?
SQL Server has been the go-to database server solution for Windows for decades. While it is about as far from a free database server solution as one can get, Microsoft does provide a zero-cost, stripped-down variant of SQL Server called SQL Express. SQL Express is an ideal learning tool for beginners as it supports the same SQL syntax that SQL Server uses. Both SQL Express and SQL Server use a custom extension of SQL called “Transact-SQL”, also known as “T-SQL.” Both SQL Express and SQL Server support the use of Windows user accounts and traditional username and password systems for access management.
Python communicates with SQL Express or SQL Server with a module named PyODBC. Both SQL Server and SQL Express are managed by a separate, zero-cost Windows application called “SQL Server Management System,” popularly known as “SSMS.” At the time of this writing, both SQL Express and SSMS are separate downloads from Microsoft:
How to Configure SQL Express for Python Development
SQL Express, like SQL Server, supports two kinds of authentication. The first is authentication based on a user’s Windows User Account, also known as a “Trusted Connection.” The second is traditional username and password-based authentication is implemented in what is called “Mixed Mode Authentication.” Mixed Mode Authentication supports both Windows User Account-based authentication and username and password-based authentication. There is no way to support username and password-based authentication by itself in SQL Server or SQL Express.
Microsoft has been moving away from Mixed Mode Authentication, as one of the major advantages of using Trusted Connections is that database credentials do not need to be stored in application code. The demonstration in this article will also not be using it.
NOT Copying the Connection String Post-Install
One point of contention for beginner-level application developers is the initial confusion surrounding SQL Server connection strings. If one is installing SQL Express, the installation program provides the connection string for the created SQL Express instance post-install. Unfortunately, the provided Connection String is likely not going to work with PyODBC. While it is tempting to be “lulled” into a sense of security with this “freebie,” it is going to cause more trouble than it is worth.
Figure 1 – Getting the Connection String from the SQL Express Installer
Note that, at the time of this writing, the installation program for SQL Express also includes a link to download the SSMS installation program.
How to Create a Database in SQL Express
Once both SQL Express and SSMS are installed, it is time to create a basic database with suitable access restrictions. The easiest way to start SSMS is to click the Start button in Windows, type in “ssms” into the search bar, wait for “Microsoft SQL Server Management Studio 18” to appear in the upper right, and then click the Open link in the right side of the Start menu panel:
Figure 2 – Starting SSMS
Upon starting SSMS, one is greeted by the following dialog box:
Figure 3 – SSMS Opening Dialog
With Windows Authentication, there is no need to enter any credentials. The Windows User Account under which SQL Express was installed has administrative privileges for the SQL Express instance. Simply click Connect to proceed.
On the far left side of the SSMS Application Window, there will be the Object Explorer. To create a new database, right click on Databases and select Create Database from the Context menu:
Figure 4 – Creating a New Database – Part 1 of 2
Clicking New Database… will open a new dialog window that allows for the particulars of the new database to be entered. For this demonstration, the database will be called RazorDemo, as a bit of a throwback to a previous article about developing Razor-based applications in C#. Enter the name of the database into the text box next to Database Name and then click the OK button at the bottom of the dialog window. Note that, in the illustration below, the columns for the Logical Name of the files were widened slightly so that the full Logical Names of the database files being created were exposed:
Figure 5 – Creating a New Database – Part 2 of 2
The new database will then appear in the Object Explorer under the Databases folder:
Figure 6 – The newly created “RazorDemo” Database
How to Create Tables in SQL Express
A relational database is not really useful without tables to store the data, and the most-straightforward way to create those tables is to use SQL code. Note, while one could use the Table Creation Wizard to create a table, using SQL code is faster, easier and far more straightforward. Start by right-clicking on the RazorDemo database entry, then left-click on the New Query option in the context window:
Figure 7 – Opening a New Query Window
A query editor window similar to the one below will appear to the right of the Object Explorer:
Figure 8 – The Query Editor Window
The table creation code is shown in the listing below:
use RazorDemo; # See the Important Note below
create table artists
(rcdid int not null identity primary key,
artist_name varchar(max));
create table albums
(rcdid int not null identity primary key,
artist_id int not null references artists(rcdid) on delete cascade,
album_name varchar(max));
Listing 1 - Table Creation SQL Code
Note, while creating a Query Editor Window from the database usually guarantees that the selected database will be the one against which the code is executed, it is a good idea to always explicitly use the intended database at the start of the code. The use command explicitly selects the name of the database that follows it.
Pressing F5 or clicking the Execute button will execute the statements against the RazorDemo database. If the execution succeeds, a message indicating so will appear in the “Messages box below:
Figure 9 – Successful Table Creation
The newly created tables and their columns can be seen in Object Explorer as well. Note that sometimes the Refresh option from the context menu that appears upon right-clicking the database may need to be selected in order to show new objects within a database:
Figure 10 – Refreshing the Object Explorer
Figure 11 – The new tables and their columns
At this point, SSMS can be safely closed.
Note that SSMS works the same way with any SQL Server database as well. It is always a best practice to save all table creation code no matter what database server is being used. Even though SQL Server and SQL allow for the recovery of such scripts, they both allow for the use of encryption on such statements as well, and in those cases, the code cannot be recovered.
Python and SQL Express
Normally, a discussion about SQL Server security would be needed here, but since Trusted Connections will be used, as long as the running process executing the Python code is owned by a Windows User that already has access to a database being accessed , that discussion will not be needed. Keep in mind that both SQL Server and SQL Express offer very robust security-related customizations, but those are beyond the scope of an article intended for beginners.
Note, do not gloss over proper database security for any application running in a production environment! Make sure that only the least possible privileges are given to the user account that would access a database in a projection environment.
The version of Python used for these code examples is 3.10, and it was installed via the Microsoft Store in Windows. Installing Python with this method will add the Python and PIP3 executables to the system path, so that the full paths to these commands will not need to be typed in the Command Prompt windows. For code entry, a good, zero-cost text editor is Notepad++.
Opening Command Prompt Windows
Executing Python code is best done via the Command Prompt. To access the Command Prompt, click the Start button in Windows and enter cmd into the search bar. Wait for Command Prompt to appear and then click the Open link on the right side of the Start Menu:
Figure 12 – Opening a Command Prompt
A typical Command Prompt window looks like this:
Figure 13 – A typical Command Prompt
How to Install PyODBC
PyODBC is the Python module which enables Python to access both SQL Server and SQL Express. Upon installation of Python via the Microsoft Store, PyODBC can be added to Python via the command:
pip3 install pyodbc
Figure 14 – Successful installation of PyODBC
Note, if there are multiple versions of Python installed, for example both Python 2 and Python 3, then it may be necessary to prefix the pip3 command with the full WIndows path to command for the appropriate version of Python.
Also note that if only Python 3 is installed, the pip3 command should still be used over the more generic pip command, as this is the proper convention.
Writing Python Code
Now that the database is configured and PyODBC are installed, the database can be populated. In the case of a database that catalogs artists and albums, some randomly generated band names and albums will suffice. The Python code to connect to the database is also included, but the inserts are not (yet):
# bad-band-name-maker.py
import sys
import random
import pyodbc
part1 = ["The", "Uncooked", "Appealing", "Larger than Life", "Drooping", "Unwell", "Atrocious", "Glossy", "Barrage", "Unlawful"]
part2 = ["Defeated", "Hi-Fi", "Extraterrestrial", "Adumbration", "Limpid", "Looptid", "Cromulent", "Unsettled", "Soot", "Twinkle"]
part3 = ["Brain", "Segment", "Audio", "Legitimate Business", "Mentality", "Sound", "Canticle", "Monsoon", "Preserves", "Hangout"]
part4 = ["Cougar", "Lion", "Lynx", "Ocelot", "Puma", "Jaguar", "Panther"]
part5 = ["Fodder", "Ersatz Goods", "Leftovers", "Infant Formula", "Mush", "Smoothie", "Milkshakes"]
def main(argv):
# Connect to the RazorDemo database.
conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=localhostSQLEXPRESS;Database=RazorDemo;Trusted_Connection=yes;")
# Generate 15 bad band names:
for x in range(1, 16):
rand1 = random.randrange(0, 9)
rand2 = random.randrange(0, 9)
rand3 = random.randrange(0, 9)
badName = part1[rand1] + ' ' + part2[rand2] + ' ' + part3[rand3]
print ("Band name [" + str(x) + "] is [" + badName + "]")
for y in range(1, 3):
rand4 = random.randrange(0, len(part4))
rand5 = random.randrange(0, len(part5))
albumName = part4[rand4] + " " + part5[rand5]
print ("tAlbum [" + albumName + "]")
# Close the Connection
conn.close()
return 0
if __name__ == "__main__":
main(sys.argv[1:])
Listing 2 - Making up some data
This gives the following output:
Figure 15 – Randomly Generated Band Names
Note the use of the cd command to change to the directory where the Python code is saved. The PyODBC connect() function will fail if the currently logged-in Windows User Account is not listed as having access in SQL Express. This is only a problem if the database is created with one Windows User Account but the code is run under a different Windows User Account.
The Least Worst Way to INSERT Data in SQL and PyODBC
Many beginner Python developers are tempted to place calls to PyODBC calls to INSERT statements in the following sections of the code, and in the context of what will be stated next, this is not a bad idea:
Figure 16 – The “almost” wrong way to work with a database
The reason why using calls to PyODBC to perform INSERTs, SELECTs, and other database-related functions, such as UPDATE or DELETE, within loops can be bad is because there is overhead that comes with each of these calls. Within a loop that could potentially iterate hundreds of times, thousands of times, or even more, this can result in a significant amount of time (minutes or more) to run through a script. For web applications which use such an approach, the performance problems compound further, as many web servers impose hard caps on the amount of time that a script can run. Never under any circumstances pass user-created inputs directly to a database. Always check the input to make sure it will not break database functionality or cause a security problem by way of a SQL Injection attack.
Ideally, one would want to use the loops above to create a SQL Batch (a list of statements) and then have PyODBC run on that single batch, but this would be a very bad idea if the data is not sanitized.
Why would the data need to be sanitized? The reason boils down to security, as user input can never be trusted. Sanitizing the data means representing it in a way that prevents anything other than the SQL statement created by the developer of the program from being executed. A user could pass a maliciously constructed string that would enable the execution of arbitrarily-created SQL code. This is known as a SQL Injection attack. While data values going into a batch can be sanitized, the process for doing so is beyond the scope of an introductory tutorial.
PyODBC provides a mechanism for protecting the database from SQL Injection attacks by sanitizing user inputs. These involve the use of parameterized statements, also referred to as prepared statements. Security must always be a priority, even if it comes at the cost of speed or other performance metrics.
The Windows User Account that has access to this database has by default has sysadmin privileges. This means that should a SQL Injection attack occur, a malicious user can gain access to all of the data in every database on the server. In practice, no account with sysadmin privileges should be accessing any database from Python code.
The listing below augments the first Python code example by using PyODBC cursors to insert the data:
# bad-band-name-maker2.py
import sys
import random
import pyodbc
part1 = ["The", "Uncooked", "Appealing", "Larger than Life", "Drooping", "Unwell", "Atrocious", "Glossy", "Barrage", "Unlawful"]
part2 = ["Defeated", "Hi-Fi", "Extraterrestrial", "Adumbration", "Limpid", "Looptid", "Cromulent", "Unsettled", "Soot", "Twinkle"]
part3 = ["Brain", "Segment", "Audio", "Legitimate Business", "Mentality", "Sound", "Canticle", "Monsoon", "Preserves", "Hangout"]
part4 = ["Cougar", "Lion", "Lynx", "Ocelot", "Puma", "Jaguar", "Panther"]
part5 = ["Fodder", "Ersatz Goods", "Leftovers", "Infant Formula", "Mush", "Smoothie", "Milkshakes"]
def main(argv):
# Connect to the RazorDemo database.
conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=localhostSQLEXPRESS;Database=RazorDemo;Trusted_Connection=yes;")
# Generate 15 bad band names, and try to keep them unique.
previousNames = ""
nameCount = 0
while (nameCount < 16):
rand1 = random.randrange(0, 9)
rand2 = random.randrange(0, 9)
rand3 = random.randrange(0, 9)
badName = part1[rand1] + ' ' + part2[rand2] + ' ' + part3[rand3]
# A crude but effective way of ensuring uniqueness, although there is no unique constraint on the artist name in the database.
# This prepends and appends bars to both the list of previously used names and the current name. If the current name is
# new, it will not be in that string.
if ("|" + previousNames + "|").find("|" + badName + "|") == -1:
print ("Band name [" + str(nameCount) + "] is [" + badName + "]")
sql1 = "insert into artists (artist_name) values (?)"
values1 = [badName]
rs1 = conn.cursor()
rs1.execute(sql1, values1)
rs1.commit()
# If the cursor is not closed, then other cursors cannot be executed.
rs1.close()
for y in range(1, 3):
rand4 = random.randrange(0, len(part4))
rand5 = random.randrange(0, len(part5))
albumName = part4[rand4] + " " + part5[rand5]
print ("tAlbum [" + albumName + "]")
sql2 = "insert into albums (artist_id, album_name) values ((select top 1 rcdid from artists where artist_name=?), ?)"
# Each array item here corresponds to the position of the ? in the SQL statement above.
values2 = [badName, albumName]
rs2 = conn.cursor ()
rs2.execute(sql2, values2)
rs2.commit()
rs2.close()
# Creates a bar-delimited list of previously used names.
if previousNames == "":
previousNames = badName
else:
previousNames = previousNames + "|" + badName
nameCount = 1 + nameCount
else:
print ("Found a duplicate of [" + badName + "]")
#print (previousNames)
# Close the Connection
conn.close()
return 0
if __name__ == "__main__":
main(sys.argv[1:])
Listing 3 - Inserting the data
The following query can be run in SSMS to verify the output of the code:
Figure 17 – The Successful Insertion of the Data
SELECTing Data in SQL Express and Python
Now that there is data in the database, it would be nice to query it. Below is a simple script that accepts user data from the keyboard passes it into the database via parameterized query:
# bad-band-name-maker3.py
import sys
import pyodbc
def main(argv):
searchValue = input("Enter something: ")
# Cap the length at something reasonable. The first 20 characters.
searchValue = searchValue[0:20]
# Set the search value to lower case so we can perform case-insensitive matching:
searchValue = searchValue.lower()
# Connect to the RazorDemo database.
conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=localhostSQLEXPRESS;Database=RazorDemo;Trusted_Connection=yes;")
# You must use a parameterized query here in order to protect from SQL Injection Attacks!
# For the like operator, the percent signs must be separated from the term or else the parameterization will fail.
sql1 = ("select a.artist_name, b.album_name from artists a, albums b where b.artist_id = a.rcdid and " +
"lower(album_name) like ('%' + ? + '%') order by a.artist_name, b.album_name")
# Below is an array with one element:
values1 = [searchValue]
rs1 = conn.cursor()
rs1.execute(sql1, values1)
rows1 = rs1.fetchone()
#print ("Type is [" + str(type(rows1)) + "]")
if str(type(rows1)).find("NoneType") == -1:
while rows1:
# Columns are indexed by number only. 0 is the a.artist_name column and 1 is the b.album_name columns
print(rows1[0] + " - " + rows1[1])
rows1 = rs1.fetchone()
else:
print ("No album name matched [" + searchValue + "]")
# Close the Connection
conn.close()
return 0
if __name__ == "__main__":
main(sys.argv[1:])
Listing 4 - Querying the Data
Same results. One even includes an example of a crude SQL Injection attack:
Figure 18 – Query Results. Note the last search value.
Final Thoughts on Python Database Development
There really is no limit to what SQL Server-driven applications can be developed using Python. A developer is only limited by knowledge of SQL, and hopefully the foundational concepts presented in this article can point a beginner-level developer in the right direction in terms of broadening one’s understanding of SQL and building more complex applications.
This Python database programming tutorial presented means by which SQL Express could be installed as a developmental stand-in for SQL Server, and showed how Python 3 can be extended in order to properly communicate with a SQL Express database instance on that server. This article also showed how SQL Server Management Studio should be used to manage any SQL Express or SQL Server databases. Going further, this article also touched upon basic security precautions and the use of efficient coding for the purposes of ensuring reasonable execution times for SQL Server driven applications.
