How To Write MySQL CRUD Queries in Python

Are you wondering how to write MySQL CRUD Queries in Python?

You will see how to install MySQL Connector/Python, a module that allows us to connect MySQL database with Python and then how to write MySQL CRUD Queries in Python. Hence, after reading this post, you will know the answer to: “How To Write MySQL CRUD Queries in Python”.

In addition, if you want to read more about CRUD queries in MySQL follow the link.

Installing Python

Before beginning with the article, it is important that you have Python installed. Additionally, it is important to note the version of Python you install (or if you already have Python installed). Python 3.9.7 is used– the latest release, for this tutorial However, you may use Python 3.7+ without any issues while following along with this tutorial.

Once you have downloaded it, head over to installing and tick the set path option. After that, you need to set up a couple more things to get started.

Create New MySQL User

Note that, this is an optional step which you may skip if you have already created a user. As I was creating a new user for the purposes of this post, I realized it might be helpful for some of you to see how it is done.

CREATE USER 'username' @ 'localhost' IDENTIFIED BY 'password';

All you have to do is swap in the actual username and password you want, and run the query on MySQL Command Line Client (CLI).

Bonus step: You can grant permissions to this user account. Again, this is an optional step and you may skip ahead.

GRANT ALL PRIVILEGES ON 'database_name' TO 'username'@'localhost'

MySQL Connection With Python

A database driver module is used to connected programming languages with databases. Use a module called MySQL Connector/Python to connect MySQL with Python. Using this, you can write MySQL code and connect the databases in Python. Moreover, as a prerequisite to using its latest version (ver 8.0), you must have Python 3.8+ installed.

Alternatively, you may use PIP to install MySQL Connector/Python with the following command:

pip install mysql-connector-python

PyMySQL

It must be known here, that while MySQL Connector/Python is the one primarily used, there are are database driver modules used for Python–MySQL connectivity, like PyMySQL, which requires Python 3.6+ and MySQL 5.6+ as prerequisites.

Establishing MySQL Connection With Python

Moving on, let’s see how to import this module and establish a connection with Python. The following block of code demonstrates this, syntactically. The first line imports this module with the use of an alias– mysql instead of writing the lengthy mysql.connector name.

import mysql.connector as mysql
mydb= mysql.connect(
  host="localhost",
  port = 3306,
  user="username",
  password="mysql_password",
)

Second key thing to note here is– 4 components or arguments pass in the .connect() method, which is used to establish the baseline connection.

These 4 arguments are:

• host: specify the host; if it’s only local, then use ‘localhost’
• port: the port MySQL listens on; usually 3306
• user: specify a MySQL username
• password: specify your MySQL password

Another important argument: database– write the name of a database that you want o connect to.

Now, I will demonstrate this connection with a real example that I used for the purposes of this blog. Although, word to the wise, don’t share your credentials with the world.

import mysql.connector as mysql
mydb = mysql.connect(
  host="localhost",
  port = 3306,
  user="sesame",
  password="opense",
)

Writing CRUD Queries In Python

This tutorial is made with the use of MySQL Connector/Python module– although you can use other database driver modules like PyMySQL.

After that, since the MySQL connection with Python is established already, let’s look at the actual process of writing CRUD queries with Python.

MYSQL Cursor Method In Python

MySQL .cursor() method executes the commands to communicate with the MySQL database

import mysql.connector as mysql

db= mysql.connect()
cursor = db.cursor()

Execute Method In Python

MySQL .execute() method is responsible for writing actual MySQL queries; everything written inside these double quotes is MySQL code.

mysql.execute("SELECT CURRENT_DATE")

Fetch Method In Python

.fetchone() fetches the next row of a query result set.

.fetchall() − It fetches all the rows in a result set.

fetch_db = cursor.fetchall()

fetch_db = cursor.fetchone()
print("Current Row: ", row[0])

Close Method In Python

This command closes the MySQL connection with Python.

mysql.close()

Summary:

Up until now, the basic code tokens/element–that are essential to most, if not all, MySQL queries in Python including connect, cursor, execute, fetch, and close– have been explained and shown with examples. You may read again if you want to clear something up, before moving to the next step.

MySQL CRUD Queries in Python

MySQL CRUD queries refer to create, read, update and delete operations.

Create MySQL CRUD Queries in Python

The CREATE and INSERT keywords are a part of Create queries. Create queries are responsible for creating objects that store data.

Creating a Database

To create a database in MySQL, use the “CREATE DATABASE” keywords followed by the database name. This is all done inside the cursor’s method .execute() which is responsible for running these queries.

db_cursor = mydb.cursor()
db_cursor.execute(CREATE DATABASE demodb)

Creating a Table

Similarly, to create a table in MySQL in Python, the MySQL syntax is used– just like above. However, now replace DATABASE keyword with TABLE. The following is an example:

cursor= mydb.cursor()
cursor.execute(CREATE TABLE mytable ( 
               id int AUTO_INCREMENT PRIMARY KEY,
               first_name VARCHAR(50),
               last_name VARCHAR(50) NOT NULL
))
               

Insert Data In a Table

Populating a table in MySQL requires use of the INSERT keyword. As such, data can be added inside our table and fill it with information. To do so, follow this syntax:

cursor= mydb.cursor()
rows = "INSERT INTO table_name VALUES (%s/d %s/d)"
values = ('one'/1, 'second', 2)
cur.execute(rows, values)

Moreover, the %s lets you add string values in Python, while %d lets you add integer values in Python. After that, you can enter values in tuple form and then you can execute the cursor query.

cursor= mydb.cursor()
rows = "INSERT INTO mytable (first_name, last_name) VALUES (%s %s)"
values = ('one'/1, 'second', 2)
cur.execute(rows, values)

Do you want to insert multiple rows of data with one code block? Well, you can do that in Python. Therefore, instead of adding values as tuples, create a list of tuples; each tuple is a row in MySQL. Consequently, this not only helps you save time, but it also makes it easier to add numerous rows of data in a short amount of time.

The .executemany() method inserts these multiple tuples of data. In addition, note that .commit() is necessary to save these new rows of data.

Read MySQL CRUD Queries in Python

READ queries are written with the SELECT keyword. These queries are essential to visualize the changes made in MySQL or to retrieve data from tables.

Select Data From Table

Selecting data from tables helps us retrieve it for numerous reasons like checking attendance records, inventories, etc. SELECT has 6 clauses: FROM, WHERE, GROUP BY, HAVING, ORDER BY.

We can select tuples or columns of data that fulfill a certain condition or all of them. To retrieve all tuples/rows of data from a SQL query, we use .fetchall() method. Moreover, you can see that the output is in tuples.

cur.mydb.cursor()
cur.execute("SELECT * FROM mytable")
rows= cur.fetchall()
for row in rows:
    print(row)

If you want to retrieve only entry of data, use .fetchone().

cur.mydb.cursor()
cur.execute("SELECT * FROM mytable")
rows= cur.fetchone()
for row in rows:
    print(row)

Update Queries In Python

Use UPDATE or ALTER keyword for Update queries..

Alter Table MySQL CRUD Queries in Python

What does it mean to alter a table? Well, let’s take an example. Imagine you created a database but your boss wants to add a new column to the database now.. But, it would be tiresome to create a new database with this column and hen populate it. Instead, you can simply use the ALTER keyword and add hat new column to your table. Similarly, you can make other type of changes like adding constraints.

cur.execute("ALTER TABLE mytable ADD address VARCHAR(255)")
mydb.commit()

It is important to note that the .commit() is necessary to save these changes to your database

Update Table In Python

If you’re in a business with real-life customers, you might come across situations where you need to update records– for eg, if an employee changes their living place, their address must be updated in the database as well to stay updated, no pun intend.

Update makes use of the clauses of SELECT keyword. Additionally, please note .commit(), which is, again, necessary to save changes.

cur.execute("UPDATE mytable SET last_name = 'dbcon' WHERE first_name = 'umar'

mydb.commit()

cur.execute("SELECT * FROM mytable")
rows = cur.fetchall()
for row in rows:
    print(row)

Don’t specify the conditions and updates right away; this makes one vulnerable to SQL injection aback. Instead, to combat that, make use of %s and %d. Therefore, define their values in a tuple and execute with the query and values.

Delete Queries In Python

Use DELETE or DROP keywords to write Delete queries. As such, this removes the records from table/database.

Delete Data From Table

Deleting data is useful and helps the database relevant, by removing unnecessary information. Hence, confirm deletion of a record with the .commit() in Python. Note that this removes all records– but the table still exists inside the memory.

cur.execute("DELETE FROM mytable WHERE last_name='dbcon'")

Drop Objects

Sometimes, you may want to drop a database or table if you made errors in defining it, or maybe the existing object type grew redundant. Similarly, you could want to make extra space in memory. As a result, use DROP keyword. After that, there is no existence of that object memory.

cur.execute("DROP DATABASE mydb")

Conclusion To MySQL CRUD Queries in Python

In the end, it has been seen how to write MySQL CRUD queries in Python, step-by-step. This tutorial is for beginners, as well as, intermediate learners who may want to complement existing knowledge in either one of Python or MySQL, with the other.

A key takeaway is that about 10 lines of each MySQL query in Python repeat– where you define the database connection and create a cursor, and finally close the database at the end. As such, it’s easy to replicate/reuse this code for other MySQL queries. The only significant changes exist in the .execute() method.

Furthermore, it is safe to say that if you are familiar with MySQL’s syntax, it’s relatively easy to write the queries in Python– even if you’re unfamiliar with Python itself.

In conclusion, I hope you will find this post useful. Please share your thoughts in the comments below. Please do check our blog there a a lot of great posts there!. Lastly, let us know your thoughts about this post in the comments. Thanks for reading.