Introduction to SQL Databases
SQL databases are the gold standard for information storage in most IT departments. SQL is generally preferred because the databases are both relational and normalized.
Table of Contents
- Introduction to SQL Databases
- The Versatility of Python Programming
- Linking Python and MySQL
- Setting Up the Connection Between Python and MySQL
- Manipulating Databases with Python
- Working with Tables in Python and MySQL
The Popularity of MySQL
MySQL is the most common SQL database system due to its open-source build. Its base is completely free, but there are many other add-ons. MySQL is easy to install, fast, and renowned for its security features.
Other SQL Database Systems
Oracle, PostgreSQL, and SQL Server are also very popular. The only possible downside of MySQL is that it doesn’t fully support every possible SQL Query, such as a FULL JOIN.
=> Join the Waitlist for Early Access.
The Versatility of Python Programming
Python is one of the most popular current programming languages. It is an object-oriented, interpreted, high-level programming language.
Python is known for its no compilation requirement, making the process of building, editing, testing, and debugging much faster than with other high-level programming languages.
IDEs for Python Development
Python can be worked on in several IDEs, the most popular being PyCharm. PyCharm has features designed to make it easy to use, offering both a professional version and a community version.
Python’s Standard Library and Applications
Python has many different applications, like data visualization tools (such as pandas and SciPy).
Different Python frameworks are useful for various tasks, such as web development, internet packages, desktop GUIs, and software and business applications.
Python’s standard library supports protocols like HTML, JSON, FTP, IMAP, and XML.
Linking Python and MySQL
SQL Syntax for Database Interaction
MySQL databases are operated by querying. For example, if you have a table named “Cook Book”, your code to select everything in the table using SQL syntax would be:
SELECT * FROM Cook Book;
Python Code for Interacting with MySQL
In some projects, Java, Python, or another language will be joined to a separate MySQL database. For example, in Python, the code to interact with a table named “Cook Book” would be:
select_food_query = "SELECT * FROM Cook Book" with connection.cursor() as cursor: cursor.execute(select_food_query) result = cursor.fetchall() for row in result: print(row)
Setting Up the Connection Between Python and MySQL
Installing MySQL on Your System
The official MySQL documentation provides instructions on how to install MySQL on the most popular operating systems.
There are instructions for Windows, macOS, and Linux.
MySQL Connector Python Installation
To connect a Python program to a MySQL database, you need a connector, also called a database driver. In this case, a MySQL driver would be called MySQL Connector Python.
This step requires pip to be installed on your computer. From there, you can install it by using:
$ pip install mysql-connector-python
Test the connection by typing this command into your Python terminal:
Manipulating Databases with Python
Connecting to a Database
To connect to a database, you’ll need something called a cursor. You can build one for yourself by using the method “connection” on a cursor variable.
from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Enter user: "), password=getpass("Enter passcode: "), database="Cook Book", ) as connection: print(connection) except Error as f: print(f)
Data Definition Language (DDL)
To create, drop, or alter a table, you have to use a Data Definition Language (DDL). Some of the basic DDL commands are CREATE, DROP, and ALTER.
Working with Tables in Python and MySQL
To create a table, input DDL code in MySQL. For example, creating a “breakfast” table would look like this:
CREATE TABLE breakfast( breakfast_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), Food_group VARCHAR(100), Calories INT );
You can combine SQL with Python like so:
create_breakfast_table_query = """ CREATE TABLE movies( breakfast_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), Food_group VARCHAR(100), Calories INT ) """ with connection.cursor() as cursor: cursor.execute(create_breakfast_table_query) connection.commit()
To insert values into a table, you can use .execute() or .executemany() depending on context. .executemany() is more commonly used since it can apply to multiple items at once.
insert_lunch_query = """ INSERT INTO lunch (name, food_group) VALUES ( %s ) """ lunch_records = [ ("bread", "grain"), ("cheese", "dairy"), ("yogurt", "dairy"), ("lettuce", "vegetable"), ("sausage", "meat"), ("tofu", "meat"), ("orange", "fruit"), ("avocado", "vegetable"), ("salmon", "meat"), ("kale", "vegetable"), ("kiwi", "fruit"), ("black beans", "protein"), ("dates", "fruit"), ("rice", "grain"), ("blackberries", "fruit"), ("melon", "fruit"), ("almond", "protein"), ("hazelnut", "protein"), ("prunes", "fruit"), ("goat cheese", "dairy"), ("agave nectar", "dressing"), ("mint", "dressing"), ] with connection.cursor() as cursor: cursor.executemany(insert_lunch_query, lunch_records) connection.commit()
Reading records is important in certain situations and can be done with a simple code. Use a select query, send it to cursor.execute(), and submit cursor.fetchall() to retrieve the table.
select_lunch_query = "SELECT * FROM lunch LIMIT 5" with connection.cursor() as cursor: cursor.execute(select_lunch_query) result = cursor.fetchall() for row in result: print(row)
Queries, specifically SQL queries in Python, are not as complicated as they may seem.
The most important parts are the wrapper for the SQL Query and a variable that represents the query.
With these tools combined, querying SQL within a Python application is a simple process. What will you use SQL and Python together to accomplish?
The organization of SQL and the power of Python is a combination that cannot be easily overstated.
Tanner AbrahamData Scientist and Software Engineer with a focus on experimental projects in new budding technologies that incorporate machine learning and quantum computing into web applications.
=> Join the Waitlist for Early Access.