How to connect the SQL server with Python

Today, a wide variety of databases are in use—including relational, NoSQL, cloud, distributed, and centralized databases.

The emphasis of this answer will be on a relational database, in this case, Structured Query Language (SQL). We'll see how to connect to it using Python, and how to query data from it.

What is SQL?

SQL is a language used to handle databases; it allows for the construction, deletion, retrieval, modification, and other operations of databases.

Popular relational database management systems include Oracle, Sybase, Microsoft SQL Server, Access, and Ingres, to name just a few.

SQL has some benefits why it is preferred by lots of developers; some of these are as follows:

  1. It makes data stored in relational database management systems accessible to users.

  2. It enables users to define and modify the data in a database.

  3. It enables the creation and deletion of databases and tables.

  4. It enables embedding into other languages utilizing pre-compilers, libraries, and SQL modules.

  5. It enables users to provide data descriptions.

We want to connect to an SQL server using Python; in order to achieve this, we will have to use a library called pyodbc.

The pyodbc library

An open-source Python library called pyodbc makes it simple to access ODBC databases. With much more Pythonic simplicity, it implements the DB API 2.0 standard.

The ODBC (Open Database Connectivity) is the standard application programming interface in computing that is used to access database management systems (DBMS).

We must note that pyodbc does not support Python 2.7, and it fully functions with Python 3.7, so we have to update our Python just in case we have Python 2 installed.

Installation

The installation of pyodbc is as easy as every other Python library. The commands to install pyodbc are given below.

#Installation with homebrew
brew install unixodbc
#installation with pip
pip install pyodbc

Connect the SQL server with Python

To connect to the SQL server using pyodbc, we will first import the pyodbc library. We will directly use the connection object connect while specifying the ODBC driver, server name, database, and so on. 

import pyodbc
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                        'SERVER=localhost;'
                        'DATABASE=testdb;'
                        'PWD=password;'
                        'Trusted_Connection=yes;')

  • Line 1: We import the pyodbc library into our workspace.

  • Line 3: We use the .connect() mehtod to specify the OBDC driver, localhost, as our server, and testdb as the name of our database. It is very important to add a password to our database for security's sake. Finally, we added a trusted connection to the names instance.

Query the SQL server with Python

Create a cursor object using the .cursor() object, which enables us to run SQL queries, comes next after setting our connection to the database.

cursor = conn.cursor()

Let's write a simple query.

cursor.execute('SELECT * FROM users')
for i in cursor:
    print(i)

  • Line 1: For us to be able to execute the queries, we use the .execute() method, and we pass in a simple English-like SQL query that basically fetches all the data from the table users.

In addition to just obtaining data from the database, this library also allows for other operations; let's have a closer look at them.

Insertion

We enter information into database tables to add data to the database. We begin by using the INSERT keyword, followed by the name of the table we want to add the data, the column(s), and finally, the data we want to add. There won't be any problems if the number of values and the number of selected columns match.

cursor.execute("INSERT INTO users(id, name) values ('100', 'Programming')")
conn.commit()

  • Line 1: We insert the values (100 and Programming) into the table users.

  • Line 2: We save all changes using the .commit() method.

Selection

We use the SQL SELECT command to retrieve data from a database table. The cursor fetch functions, fetchone(), fetchall(), and fetchmany(), can be used to retrieve data.

cursor.execute("SELECT id, name from users")
row = cursor.fetchone()
if row:
    print(row)

  • Line 1: We loop through the table users and select the columns id and name.

  • Line 2: We create a variable row with a fetchone() function.

  • Line 3: We used an if statement here, to check if the selected columns contains any data, and if it does it should print it.

Update

The existing data or record in a table can be changed using the UPDATE command.

cursor.execute("UPDATE users SET name='Benjamin' WHERE id=1").rowcount
conn.commit()

Line 1: We update the name of the user with ID, 1, in the database and changed it to 'Benjamin' using the UPDATE / SET keywords.

  • Line 2: We save all changes using the .commit() method.

Delete

delete = cursor.execute("DELETE FROM users WHERE id=1").rowcount
conn.commit()

  • Line 1: Using the cursor execute(), we delete the user with id 1 from our database with the keyword DELETE.

  • Line 2: We save all changes using the .commit( ) method.

Convert SQL to pandas DataFrame

We can go one step further by converting from SQL to pandas DataFrame using the pd.read_sql_query() method to demonstrate the additional strength and flexibility of this library.

import pyodbc 
import pandas as pd
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                      'SERVER=localhost;'   
                      'DATABASE=testdb;
                      'Trusted_Connection=yes;')
dframe = pd.read_sql_query('SELECT * FROM users', conn)
print(dframe)

  • Line 1: We import pyodbc into our workspace.

  • Line 2: We import pandas into our workspace but give it an alias of pd.

  • Line 4: As discussed earlier, we specify the Driver, Server, and Database in the .connect() method. We didn't add a Password to this because this is just a tutorial, and we want to see our output immediately.

  • Line 9: With the help of the pd_read_sql_query() method, we create our DataFrame. The method takes two parameters, the SQL query and the connection object.

  • Line 10: We print our DataFrame dframe to see the output.

Conclusion

We've now seen how to connect Python to the SQL Server; the pyodbc package made this all feasible. To handle our data, we can use SQL in Python once we have installed it and made a connection. SQL is a crucial tool for data analysis and analytics; therefore, having a working grasp of it might lead to a lucrative profession.

There is a course on Educative on how to become professional with SQL, we strongly recommend it.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved