This segment is a bit advanced, so those who have no knowledge of databases or are just starting with Python may skip this part.
Python’s database interface is very rich and supports many database systems. Some notable and commonly used database systems include:
- MySQL
- Oracle
- Microsoft SQL Server
- PostgreSQL
- Microsoft Office Access
Here, we will discuss the highly popular database system, MySQL.
For this, we need MySQL and the Python MySQL database API installed on our system.
Assuming MySQL is already installed on your system, it is also helpful to have PHPmyAdmin installed. It is invaluable for visualizing changes in the database. If not installed, I recommend installing XAMPP, which includes these tools. Download link here.
Verifying MySQLdb API Installation
Let’s write a program to check if the MySQLdb API is installed on our system:
import MySQLdb
If you get an error like:
ModuleNotFoundError: No module named 'MYSQLdb'It means the MySQLdb module is not installed. To install it, run the following command:
pip install MySQL-python
If everything is correct, it will be installed.
Creating a Database Connection
Now let’s see how to connect to a database. Since we are using our system’s server, the default host is localhost, the username is root, and the password is empty.
To establish a connection, write the following code:
# This is an example for establishing database connection in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","")
print(conn)
The result will be:
<_mysql.connection open to 'localhost' at 346ed18>This means the connection is successfully established.
Here, note that we use localhost as the host. You can also use the IP 127.0.0.1. Sometimes, you may need to use a remote server, in which case you must provide the server’s URL. For example, to connect to this website’s database, you would use www.shahinur.com as the host.
Creating a Database
Once the database connection is established, we can perform any database operation. Let’s create a database:
# This is an example for creating a database in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","")
createcursor = conn.cursor()
createcursor.execute("CREATE DATABASE student")
This code creates a database named student. If you have PHPmyAdmin installed, you can see the database by navigating to localhost/phpmyadmin in your browser.
The cursor() object allows us to perform different tasks using the same connection. The execute() method executes SQL queries.
Creating a Table
We just created a database, now let’s create a table in that database:
# This is an example for creating a table in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","","student") # Need to mention the database name
createcursor = conn.cursor()
createcursor.execute("CREATE TABLE info (id INT, name VARCHAR(500), dept VARCHAR(200))")
If everything is correct, a table named info will be created in the student database.
Remember, a table cannot exist by itself; it must be created under a database. Hence, in line 3, we mention the student database name.
In line 6, the command to create the table is written. The info table will have id, name, and dept. Here, INT and VARCHAR are data types indicating the type of data the columns can hold.
SQL is not dynamically typed like Python, so defining data types is necessary. More details about data types can be found here.
To verify the table’s existence or to see the list of tables, use the following code:
# This is an example for showing tables in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","","student") # Need to mention the database name
createcursor = conn.cursor()
createcursor.execute("SHOW TABLES") # Show table query
for i in createcursor: #diplay table using loop in case of multiple tables
print(i)
Result:
('info',)This confirms that the table we created exists. The loop displays the tables because a database can have multiple tables.
Insert Operation
We created a table; now let’s insert values into it:
# This is an example for inserting value to a table in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","","student") # Need to mention the database name
createcursor = conn.cursor()
createcursor.execute("INSERT INTO info (id, student_name, dept) VALUES(1,'SHAHINUR','CSE')") # Insert query
conn.commit()
The value is successfully inserted into the database. In line 6, we wrote an SQL statement directly. The values must match the column names and follow a specific order. To insert values into a table, the INSERT INTO command is used.
In line 8, the commit() function is used. It is mandatory; otherwise, no changes will be made to the database.
Read Operation
We just inserted a value; now let’s see how to read or view values in the table:
# This is an example for reading value from a table in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","","student") # Database connection
createcursor = conn.cursor()
createcursor.execute("SELECT * FROM info") # Select query
for i in createcursor:
print(i)
Output:
(1, 'SHAHINUR', 'CSE')
(2, 'RAHIM', 'IPE')
(3, 'KARIM', 'MB')This means our database has information for three people.
The SELECT * FROM info statement fetches all the data. Here, * means ALL, i.e., it will select everything in the database.
Where Clause
Suppose we don’t want all the information from the database, but only the information of students from the CSE department. In this case, we use the where condition.
Let’s see an example:
# This is an example for reading a specific value from a table in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","","student") # Database connection
createcursor = conn.cursor()
createcursor.execute("SELECT * FROM info WHERE dept='CSE'") # Select query with where condition
for i in createcursor:
print(i)
Output:
(1, 'SHAHINUR', 'CSE')This means our database has only one student in the CSE department.
The where condition is used to specify a condition or filter.
Update Operation
Now, let’s see how to modify or update information.
Suppose Karim is actually from the GEBT department but was mistakenly listed under MB. We will update MB to GEBT:
# This is an example for updating a specific value from a table in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","","student") # Database connection
createcursor = conn.cursor()
createcursor.execute("UPDATE info SET dept = 'GEBT' WHERE student_name = 'KARIM'") # Select query with where condition
conn.commit()
The value MB is updated to GEBT in the database. In line 6, the update query is written. If translated directly, it means:
Update the info table where the name is KARIM and set the dept to GEBT instead of MB.
Delete Operation
Now let’s learn how to delete. Suppose the information for RAHIM was mistakenly entered into the database. We will delete it.
# This is an example for deleting a specific value from a table in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","","student") # Database connection
createcursor = conn.cursor()
createcursor.execute("DELETE FROM info WHERE student_name='RAHIM'") # Delete query with where condition
conn.commit()
The information for RAHIM will be deleted from the info table.
Drop Operation
Drop means to delete. In MySQL, the drop command is used to delete any table or database.
Now, let’s delete the info table:
# This is an example for dropping a table in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","","student") # Database connection
createcursor = conn.cursor()
createcursor.execute("DROP TABLE IF EXISTS info") # drop query
conn.commit()
The info table will be deleted.
Now, let’s delete the student database:
# This is an example for dropping a database in python
import MySQLdb
conn=MySQLdb.connect("localhost","root","","student") # Database connection
createcursor = conn.cursor()
createcursor.execute("DROP DATABASE student") # drop query
conn.commit()
The database will be deleted.
The most important thing here is to have an understanding of SQL. Any query can be executed within the execute() function.