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

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.

Creating a Python PostgreSQL connection for absolute beginners

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