How to Make a Database Connection in Python for Absolute Beginners

3 steps (+examples) to connect to MS SQL Server, MySQL, Oracle and many other databases

Mike Huls

Jul 4, 2022 — 8 min read

Time to connect (image by Marcus Urbenz on Unsplash)

With Python we can automate writing and executing SQL. But for this to work we need Python to be able to communicate with the database. In this article we’ll focus on using a package called pyodbc to communicate with relational databases using the ODBC protocol. After reading this article you’ll be able to write and execute SQL in your Python app. Let’s code!

Before we begin..

Let’s define the scope of this article.

Postgres user?

There are many databases compliant with ODBC and pyodbc and in this article we’ll use MS SQL. If you are using PostgreSQL then check out the article below for a more optimized approach.

Generating SQL queries

This article focusses on creating a connection with a database using pyodbc. This connection can then be used to execute SQL. Some databases use other syntax than others:

MS SQL Server
SELECT TOP 1 colname FROM tablename LIMIT 10
PostgreSQL
SELECT colname FROM tablename LIMIT 10.

This means that you have to write the SQL statements for a specific database.

There is a database-agnostic approach where you define your query in a more Pythonic way and then compile if for a certain database. This will generate the SQL specific for that database so that you are not bound to the specifics of the database you’re currently using. This means that if you decide to switch from MySQL to Postgres in the future you won’t have to change your code.

I’m currently writing this article so make sure to follow me!

About pyodbc and the ODBC protocol

A little background on the package we’re using and how it works. Pyodbc is a package that allows you to communicate with (relational) databases. It uses the Open DataBase Communication protocol. This protocol defines how a client (like your Python script) and the database communicate.

You can compare this communication protocol to the HTTP protocol that facilitates communication between computers over the internet: clients know how to ask for resources, servers know how to respond and clients know what the response looks like so they can use the information. In the same way clients can use the ODBC protocol to communicate with databases.

Connecting to a database — the code part

There are a few simple steps that we’ll go through. We’ll need to use our credentials to create a connection string. With that string we’ll make a connection. On the connection you can create a cursor that we will use to execute queries. First some preparations:

Prep: dependencies

First let’s create a virtual environment and install our only dependency.pip install pyodbc

Step 1. Collect our credentials

This is where we retrieve the credentials for our database. In the example below we handle our credentials in the safest way possible: we load them from the environment so that our application has access without hardcoding them into our script.import pyodbc

driver: str = 'ODBC Driver 17 for SQL Server'
username = os.environ.get("SQLSERVER_USER")
password = os.environ.get("SQLSERVER_PASS")
host = os.environ.get("SQLSERVER_HOST")
port = os.environ.get("SQLSERVER_PORT")
database = os.environ.get("SQLSERVER_DB")

Prevent your passwords from leaking by learning how to apply env files in the article below:

Step 2. Create the connection string

Pyodbc needs a formatted string containing our credentials to connect to the database. In the example below we use an f-string to create the connection string while keeping our code clean.password = "}}".join(password.split("}"))

constring = f"DRIVER={driver};" \
f"SERVER={host};" \
f"DATABASE={database};" \
f"UID={username};" \
f"PWD={{{password}}};" \
f"port={port};"

Fixing password..
Something weird is going on with password. First we double up all } curly-brackets in the password, then we surround it with curly-brackets again. This is because the password can contain some weird characters like /\{}; for example. Surrounding them with curly-brackets will escape all of them. We don’t want any curly-brackets that are part of our password to be escaped so we double them up in order to “escape the escaping”.

Bit weird but this is how it works apparently. Also notice that this not only works with password but with any argument so if your username contains special characters you can also use this technique.

The resulting connection string looks like this (notice that my_}password is now correctly translated to {my_}}password}).DRIVER=ODBC Driver 17 for SQL Server;SERVER=my_host;DATABASE=my_database;UID=my_username;PWD={my_}}password};port=my_port;

Step 3. Connecting

This is the easy part we’ll create a connection (usually you have one connection per application). Then we create a cursor on the connection. A cursor is used for iterating over a result set resulting from a query. You close the cursor when you’re done with the result set.cnxn:pyodbc.Connection = pyodbc.connect(constring)cursor:pyodbc.Cursor = cnxn.cursor()
try:
cursor.execute("SELECT @@VERSION")
print(cursor.fetchone())
except Exception as e:
print(f"Connection could not be established: {e}")
finally:
cursor.close()

Next we can use the cursor to execute some SQL, in this case we print out the version of our SQL Server database. If anything fails we print out the error and in any case we close our cursor.

There’s a shorter, better way to do this:with cnxn.cursor() as cursor:
try:
cursor.execute("SELECT @@VERSION")
print(cursor.fetchone())
except Exception as e:
print(f"Connection could not be established: {e}")

Using the context-manager (the with part) causes the cursor to automatically close. Additionally it will commit (you have to commit inserts; see examples below) anything you execute within the try block. If it detects errors it will rollback all queries. Note that this only works if the connection is created with autocommit=False (which is the default).

Bonus: Example queries

Here are some example queries to get you going.

Query 1: Selecting records

Use cursor.fetchone() to retrieve a single row.with cnxn.cursor() as cursor:
cursor.execute("SELECT * FROM INFORMATION_SCHEMA.TABLES")
for row in cursor.fetchall():
print(row)

Query 2: Selecting records; convert to a list of dictionaries where each dictionary is a rowwith cnxn.cursor() as cursor:
cursor.execute("SELECT * FROM INFORMATION_SCHEMA.TABLES")
colnames = [col[0] for col in cursor.description]
coltypes = [col[1] for col in cursor.description]
for rowvalues in cursor.fetchall():
# convert types
rowvalues = [coltypes[i](rowvalues[i]) for i in range(len(coltypes))]
# make dicts from the colnames and rowvalues
row = dict(zip(colnames, rowvalues))
print(row)

Query 3: Regular insert

Because we are within the context-manager-block (the with) the execute will be committed if no errors occur. Also the cursor is auto-closed.with cnxn.cursor() as cursor:
cursor.execute("INSERT INTO med.mytable (name, age) VALUES (?,?)", "mike", 32)

Query 4: Rolling back an insert

The first two get inserted, then #3 fails so #1 and # 2 get rolled back again. There will be no trace of mr. a, b or c in the database.with cnxn.cursor() as cursor:
cursor.execute("INSERT INTO med.mytable (name, age) VALUES (?,?)", "mr. a", 44)
cursor.execute("INSERT INTO med.mytable (name, age) VALUES (?,?)", "mr. b", 33)
cursor.execute("INSERT INTO med.mytable (name, age) VALUES (?,?)", "mr. c", 55, "toomany")

Query 5: Superfast insert

The inserts above will insert one row at a time. With the option below we can create one statement for multiple rows, greatly improving the insertion speed.with cnxn.cursor() as cursor:
cursor.fast_executemany = True
SQL = "INSERT INTO med.mytable (name, age) VALUES (?,?)"
values = [
('mr. y', 21),
('mr. x', 98)
]
cursor.executemany(SQL, values)

Read more in the article below to learn about the inner workings of fast_executemany.

Important

Always make sure to clean the variables you put into your SQL statements in order to prevent SQL injection attacks, especially if you allow user input into your statements. This is beautifully explained by this famous XKCD comic.

Next steps

Now that your script can connect to the database you can start writing SQL. You can make an API in 5 lines of code for example. This way you can give users controlled access to your database, defining which information users may request.

Make sure to check out this link for a nice overview of many useful queries. Lastly, the article below that details how to implement database migrations. These make it possible to programmatically design and version control your database.

Conclusion

I hope to have shed some light on how to connect to your database with pyodbc and use it to execute some SQL. I hope everything was as clear as I hope it to be but if this is not the case please let me know what I can do to clarify further. In the meantime, check out my other articles on all kinds of programming-related topics like these:

Happy coding!

— Mike

P.S: like what I’m doing? Follow me!