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:
It makes data stored in relational database management systems accessible to users.
It enables users to define and modify the data in a database.
It enables the creation and deletion of databases and tables.
It enables embedding into other languages utilizing pre-compilers, libraries, and SQL modules.
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 homebrewbrew install unixodbc#installation with pippip 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 pyodbcconn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};''SERVER=localhost;''DATABASE=testdb;''PWD=password;''Trusted_Connection=yes;')
Line 1: We import the
pyodbclibrary into our workspace.Line 3: We use the
.connect()mehtod to specify the OBDC driver,localhost, as our server, andtestdbas the name of our database. It is very important to add apasswordto 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 tableusers.
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 (
100andProgramming) into the tableusers.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
usersand select the columnsidandname.Line 2: We create a variable
rowwith afetchone()function.Line 3: We used an
ifstatement 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").rowcountconn.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").rowcountconn.commit()
Line 1: Using the cursor
execute(),we delete the user withid1 from our database with the keywordDELETE.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 pyodbcimport pandas as pdconn = 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
pyodbcinto 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, andDatabasein the.connect()method. We didn't add aPasswordto 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
printour DataFramedframeto 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
