SQL and Python are two widely used technologies in the field of analytics. SQL allows you to communicate with relational databases, and Python helps you analyze and visualize data by using various libraries. But did you know you can use SQL with Python to write queries and solve specific problems?
In this SQL with Python tutorial, you’ll learn to write SQL queries in the Jupyter Notebook with Python-like syntax. If you don’t have MySQL Workbench or Jupyter Notebook installed, please install them first.
You will cover some of the crucial SQL commands and see how to store, process, analyze and retrieve data using SQL with Python.
Start by importing the libraries.
In the next step, connect to MySQL Server from the Jupyter notebook. You must use exception handling techniques to connect to your MySQL server. The try block lets you test a block of code for errors. The except block will handle the errors.
Here, there is a user-defined function called create_server_connection. It has three parameters - host_name, user_name, user_password.
The mysql.connector.connect() method sets up a connection. It establishes a session with the MySQL server. If you pass no arguments, it uses the already configured or default values. When the connection is fine, the function will return a connection object.
Now, create mysql_python database by defining a create_database() function.
The MySQL connection.cursor() method returns a MySQLCursor() object, or a subclass of it depending on the arguments that you pass.
The next step is to connect to the mysql_python database. To do this, you need to create a function called create_db_connection.
Now, create a function to execute your query. It takes two parameters - connection and query. The cursor.execute() method executes the given database query/command.
The connection.commit() method sends a COMMIT statement to the MySQL server for committing the current transaction. It is crucial to call this method after every transaction that manipulates data for tables that use transactional storage engines.
So far, you saw how to establish the MySQL server connection and create a database. Now, create a table called Orders with seven columns.
Assign the SQL query to a Python variable using triple quotes. This will create a multi-line string.
The table below has the following columns:
- Order_id
- Customer_name
- Product_name
- Date_ordered
- Quantity
- Unit_price
- Phone_no
Now that the table is ready, insert a few records into the table using the insert command.
You can check the table on the MySQL workbench by running a simple select query.
In order to read data from the table, create a read_query function containing the fetchall() method. This method fetches rows of a SQL query result set and returns a list of tuples. If the rows are unavailable, it returns an empty list.
Now, explore some of the popular commands to understand SQL with Python in detail.
- Display all the records in the Orders table:
- Display specific columns from the table:
- Use distinct to display the unique years’ values in the date_ordered column:
- Query to return the records of the items ordered before Dec 31, 2018:
- SQL query to print the records in descending order of unit price:
- You can also create a Pandas data frame from the existing records in the table. To do this, you can append the result to an empty list, assign the column name, and use the Dataframe() method to build the data frame.
- SQL query to update the record with order id 103 and set its unit price to 45:
- See how the Delete command works. You must delete the details for order id 105:
- The query below shows that the record with order_id 105 is no more present in the table.
Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!
Conclusion
After reading this tutorial, you have learned how to connect to MySQL using Python Jupyter Notebook. You got an idea to write SQL queries with Python.
Do you have any questions regarding this SQL with Python tutorial? If you do, then please put them in the comments section. We’ll be happy to answer any questions.
To get certified in SQL, check out this SQL course.
