This blog will teach you how you can work with MySQL in Python.
SQL is the world’s most used data storage/querying language — and Python the most popular programming language in the world. Both together can produce some spectacular results.
There’s no default Python SQL module that you can use to connect to a MySQL database. Instead, you’ll need to install a Python SQL driver for MySQL in order to interact with a MySQL database from within a Python application.
Note that MySQL is a server-based database management system. One MySQL server can have multiple databases. Unlike SQLite, where creating a connection is tantamount to creating a database.
Procedure To Follow In Python To Work With MySQL
- Connect to the database.
- Create an object for your database.
- Execute the SQL query.
- Fetch records from the result.
- Informing the Database if you make any changes in the table.
We will discuss all the above steps later.
Installing MySQL
MySQL is one of the most popular databases.
We recommend that you use PIP to install “MySQL Connector”. PIP is most likely already installed in your Python environment. Navigate your command line to the location of PIP, and type the following:
python -m pip install mysql-connector-python
Now you have downloaded and installed a MySQL driver.
Test MySQL Connector
To test if the installation was successful, or if you already have “MySQL Connector” installed, create a Python page with the following content:-
import mysql.connector
If the above code was executed with no errors, “MySQL Connector” is installed and ready to be used.
Create Connection
Start by creating a connection to the database.
Use the username and password from your MySQL database:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456" ) print(mydb)
Now you can start querying the database using SQL statements.
Creating a Database
To create a database in MySQL, use the “CREATE DATABASE” statement:
Example:
create a database named “mydatabase0”:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456" ) mycursor = mydb.cursor() mycursor.execute("CREATE DATABASE mydatabase0")
If the above code was executed with no errors, you have successfully created a database.
Check if Database Exists
You can check if a database exists by listing all databases in your system by using the “SHOW DATABASES” statement:
Example
Return a list of your system’s databases:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456" ) mycursor = mydb.cursor() mycursor.execute("SHOW DATABASES") for x in mycursor: print(x)
Creating a Table
Example:
To create a table in MySQL, use the “CREATE TABLE” statement. Make sure you define the name of the database when you create the connection
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456", database="mydatabase0" ) mycursor = mydb.cursor() mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
If the above code was executed with no errors, you have now successfully created a table.
Insert Into Table
To fill a table in MySQL, use the “INSERT INTO” statement.
Example:
Insert a record in the “customers” table:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456", database="mydatabase0" ) mycursor = mydb.cursor() sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" val = ("John", "Highway 21") mycursor.execute(sql, val) mydb.commit() print(mycursor.rowcount, "record inserted.")
Note: Notice the statement: mydb.commit()
. It is required to make the changes, otherwise, no changes are made to the table.
Select From a Table
To select from a table in MySQL, use the “SELECT” statement:
Example:
Select all records from the “customers” table, and display the result:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456", database="mydatabase0" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM customers") myresult = mycursor.fetchall() for x in myresult: print(x)
Note: We use the fetchall()
method, which fetches all rows from the last executed statement.
Select With a Filter
When selecting records from a table, you can filter the selection by using the “WHERE” statement:
Example:
Select record(s) where the address is “Highway 21”: result:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456", database="mydatabase0" ) mycursor = mydb.cursor() sql = "SELECT * FROM customers WHERE address ='Highway 21'" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
Sort the Result
Use the ORDER BY statement to sort the result in ascending or descending order.
The ORDER BY keyword sorts the result ascending by default. To sort the result in descending order, use the DESC keyword.
Example:
Sort the result alphabetically by name: result:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM customers ORDER BY name" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
Delete Record
You can delete records from an existing table by using the “DELETE FROM” statement:
Example:
Delete any record where the address is “Mountain 21”:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456", database="mydatabase" ) mycursor = mydb.cursor() sql = "DELETE FROM customers WHERE address = 'Mountain 21'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) deleted")
Note: Notice the statement: mydb.commit()
. It is required to make the changes, otherwise no changes are made to the table.
Delete a Table
You can delete an existing table by using the “DROP TABLE” statement:
Example:
Delete the table “customers”:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456", database="mydatabase" ) mycursor = mydb.cursor() sql = "DROP TABLE customers" mycursor.execute(sql)
Update Table
You can update existing records in a table by using the “UPDATE” statement:
Example:
Overwrite the address column from “Highway 21” to “Canyoun 123”:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456", database="mydatabase" ) mycursor = mydb.cursor() sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Highway 21'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) affected")
Limit the Result
You can limit the number of records returned from the query, by using the “LIMIT” statement:
Example:
Select the 5 first records in the “customers” table:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456", database="mydatabase" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM customers LIMIT 5") myresult = mycursor.fetchall() for x in myresult: print(x)
Join Two or More Tables
You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement.
Consider you have a “users” table and a “products” table:
Users:
{ id: 1, name: 'John', fav: 154}, { id: 2, name: 'Peter', fav: 154}, { id: 3, name: 'Amy', fav: 155}, { id: 4, name: 'Hannah', fav:}, { id: 5, name: 'Michael', fav:}
Products:
{ id: 154, name: 'Chocolate Heaven' }, { id: 155, name: 'Tasty Lemons' }, { id: 156, name: 'Vanilla Dreams' }
These two tables can be combined by using users’ fav
field and products’ id
field.
Example:
Join users and products to see the name of the users favourite product:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="123456", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT \ users.name AS user, \ products.name AS favorite \ FROM users \ INNER JOIN products ON users.fav = products.id" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x)
Note: You can use JOIN instead of INNER JOIN. They will both give you the same result.
So this was a brief overview of how you can set up SQL with Python and perform some basic SQL operations.
Related References
- Python For Beginners: Overview, Features & Career Opportunities
- An Introduction To Python For Microsoft Azure Data Scientist | DP-100
- Python For Data Science: Why, How & Libraries Used
- Data Scientists vs Data Engineers vs Data Analyst
Next Task For You…
Data science is a rapidly growing field, and the demand for data science skills and expertise is expected to continue to increase in the coming years. The exponential growth of data has been staggering in recent years.
As data becomes increasingly important in driving business value, the demand for data science professionals is expected to continue to grow in the coming years.
Begin your journey toward becoming a Data Science Expert. Join our FREE CLASS on How to Build a Career in Data Science
Leave a Reply