We can interact with databases using any programming language, or we can use a software program that allow us to interact with the database using a GUI. In this article, we will discuss databases and show you how to interact with them using the Python programming language.
Relational Databases (SQL)
Relational databases (SQL) are different from non-relational databases (NoSQL) in terms of schema. A schema is a template that defines the structure of the data you are going to store. In relational databases, we create tables to store data. The schema of a table is defined when the table is created. For example, if we want to store data on students in a relational database, then we will create a table of students and define the schema of the table, which might include the name, registration number, grade, etc. of each student. After creating the schema, we will store the data in the rows of the table. It is important to note that we cannot store data that is not defined in the schema. In this example, the grade a student received on an exam cannot be stored in the table because we have not defined a column for these data in the schema.
The following list includes some popular relational databases:
- MariaDB
- MySQL
- SQL Server
- PostgreSQL
- Oracle
Non-Relational Databases (NoSQL)
As discussed above, non-relational databases do not have a defined schema. Non-relational databases have collections instead of tables, and these collections contain documents that are equivalent to the rows in a relational database. For example, if we want to create a non-relational database to store student data, we can create a collection of users and, in this collection, we will store a document for each student. These documents do not have a defined schema, and you can store anything you want for each student.
Performing CRUD Operations in MySQL
Now, we will show you how to interact with MySQL using Python.
Installing MySQL Driver for Python
To interact with MySQL using Python, we first need to install MySQL driver in Python.
or
Creating a Database
Before creating a database, we need to connect with MySQL server using Python. The mysql.connector module offers the connect() method to help to establish a connection with MySQL using Python.
//Replace with your own IP and Server Credentials
>>> sql = mysql.connector.connect(
... host='localhost',
... user='root',
... password='12345'
... )
>>> print(sql)
<mysql.connector.connection_cext.CMySQLConnection object at 0x7fccb1190a58>
This message shows that we have successfully created a connection with a MySQL database using Python. Now, we will run an SQL query on MySQL server using the execute() method from the mysql.connector module.
>>> query = ‘CREATE DATABASE demo_db’
>>> cursor.execute(query)
The above code will create a database named demo_db in MySQL.
Creating a Table
Now that we have created a database, we will create a new table named students. To create a table, we need to connect to the database.
... host='localhost',
... user='root',
... password='12345',
... database='demo_db'
... )
After connecting to the database, we will use the execute() method to run an SQL query to create a table with a schema.
>>> cursor.execute(query);
The above command will create a table named students in the demo_db database; we can insert only a name, id, grade and date of birth in the table, as defined in schema.
Inserting Rows into a Table
Now that we have created a table, we will insert a student in this table. We will create a query and then use the execute() method to run the query on MySQL server using Python.
>>> cursor.execute(query)
>>> sql_db.commit()
This query will add a student with the data defined in the query into the table. We can add additional students to the table in the same way.
NOTE: Changes will be applied to the database only if you run sql_db.commit() after applying changes.
Selecting Rows from a Table
The SELECT statement in MySQL is used to return data from a table. We will use the execute() method to run a query, and then we will use the fetchall() method to get a list of all students. Then, we can use a for loop to display all the students
>>> cursor.execute(query)
>>> result = cursor.fetchall()
>>> for x in result:
... print(x)
('John', 1, 3, datetime.date(2020, 7, 4))
We can see that only data for a single student data are returned, as we have only one student in the table. We can use the WHERE statement in MySQL with the SELECT statement to specify constraints. For example, if we want to return the students in grade 4 only, we can use the following query:
>>> cursor.execute(query)
>>> result = cursor.fetchall()
>>> for x in result:
... print(x)
The above code will fetch only the students from grade 4.
Updating a Row
In this section, we will show you how to update the student data in a MySQL table using Python. We will use the UPDATE statement with the WHERE and SET statements in MySQL to update the data of specific students. The WHERE statement is used to determine which rows will be updated, and the SET statement is used to defines the values used for the update.
>>> cursor.execute(query)
>>> sql_db.commit()
Now, we will try to read the student data from the table by using the SELECT statement.
>>> cursor.execute(query)
>>> for x in cursor:
... print(x)
('Mark', 4, 4, datetime.date(2020, 7, 15))
Now, we can see that the name of the student with id 4 has been changed to Mark.
Deleting a Row
We can delete a row from the table by applying the DELETE statement in MySQL using Python. We will use a DELETE statement with a WHERE statement to delete specific students from the table.
>>> cursor.execute(query)
>>> sql_db.commit()
Now, we can return all the students from the table using the SELECT statement.
>>> cursor.execute(query)
>>> for x in cursor:
... print(x)
('John', 1, 3, datetime.date(2020, 7, 4))
('John', 3, 3, datetime.date(2020, 7, 8))
('Mark', 4, 4, datetime.date(2020, 7, 15))
We can see that the table does not contain a student with an id of 2, as we have removed the student from the table.
Dropping a Table
The mysql.connector module can also be used to drop a table. We can execute a DROP statement in MySQL by using the execute() method.
>>> query = 'DROP TABLE students'
>>> cursor.execute(query)
The above code will delete the table named students when executed in Python.
That concludes our discussion of SQL databases. We have showed you how to apply different queries to the MySQL database using Python. Next, we will apply CRUD operations to a NoSQL Database called MongoDB
Performing CRUD Operations in MongoDB
To interact with MongoDB using Python, we must first install pymongo, which is a MongoDB driver for Python.
or
Creating a Database
We can connect to MongoDB using the MongoClient() method of the pymongo module in MongoDB. Before performing any actions, we need to connect to the MongoDB database.
>>> client = pymongo.MongoClient('mongodb://localhost:27017/')
After connecting to the datacase, we can execute the following line to create a new database named demo_db.
If the database already exists, then this command is ignored.
Creating a Collection
Now that we have created a database, we will create a collection named students in the database named.
>>> client = pymongo.MongoClient('mongodb://localhost:27017/')
>>> db = client['demo_db']
>>> col = db['students']
NOTE: MongoDB does not create a collection until you enter data in it. Therefore, if you try to access the collection after running the above code, you will find that there is nothing in the database.
Unlined MySQL, we do not have to define a schema when we create a new collection, as MongoDB is a non-relational database.
Inserting a Document
After creating a collection, we can insert a document inside the collection. First, we must define a dictionary, and then we can use the insert_one() method to insert the data defined in the dictionary into the collection.
NOTE: MongoDB automatically creates a unique ‘_id’ for each document; therefore, we do not need to specify an id.
... "name": "John",
... "grade": 3,
... "dob": "2020-04-03"
... }
>>> result = col.insert_one(data)
In the above document, we inserted name, grade and dob. Now, we will insert a document in the students collection that has a field for age.
... "name" : "Mark",
... "grade": 4,
... "dob": "2020-04-09",
... "age" : 8
... }
>>> result = col.insert_one(data)
We can see that this command does not throw an error. Because MongoDB is a non-relational database, we can add any information we want in the document.
Getting Documents
In this section, we will use the find() and find_one() methods to get data from the database. The find() method takes two arguments: the first is used to filter documents, and the second is used to define the fields of the document we want to return. For example, if we want to get the id of ‘John,’ then we can run the following query:
>>> for x in result:
... print(x)
{'_id': ObjectId('5f8f0514cb12c01f7420656e')}
Alternatively, we can get all the documents from the collection by using the following query:
>>> for x in result:
... print(x)
{'_id': ObjectId('5f8f0514cb12c01f7420656e'), 'name': 'John', 'grade': 3, 'dob': '2020-04-03'}
{'_id': ObjectId('5f8f061ccb12c01f7420656f'), 'name': 'Mark', 'grade': 4, 'dob': '2020-04-09', 'age': 8}
Updating Documents
The pymongo module offers the update_one() and update_many() methods for updating the documents in a collection. Both methods take two arguments: the first defines which document to change, and the second defines the new values. Now, we will change the grade of the student ‘Mark’.
>>> value = {"$set": {"grade": 5}}
>>> col.update_one(query, value)
>>> for x in col.find():
... print(x)
{'_id': ObjectId('5f8f0514cb12c01f7420656e'), 'name': 'John', 'grade': 3, 'dob': '2020-04-03'}
{'_id': ObjectId('5f8f061ccb12c01f7420656f'), 'name': 'Mark', 'grade': 5, 'dob': '2020-04-09', 'age': 8}
Deleting a Document
The pymongo module in Python has two methods, i.e., delete_one() and delete_many(), for deleting documents. Both methods take an argument that selects the document to delete. With the following code, we will delete a student named ‘John’.
>>> col.delete_one(query)
>>> for x in col.find():
... print(x)
{'_id': ObjectId('5f8f061ccb12c01f7420656f'), 'name': 'Mark', 'id': 2, 'grade': 5, 'dob': '2020-04-09', 'age': 8}
Dropping a Collection
We can drop a collection in MongoDB by using the drop() method of the pymongo module in Python. First, we need to connect to the database; then, we select the database that holds the collection we want to remove. After selecting the collection from the database, we can remove the collection using the drop() method. The following code will drop students.
>>> client = pymongo.MongoClient('mongodb://localhost:27017/')
>>> db = client['demo_db']
>>> col = db['students']
>>> col.drop()
Conclusion
Knowledge of databases is essential if you want to make a web application. Almost every programming language has frameworks and libraries for backend web development. Python can be used in backend web development, and so we can interact with databases using Python while working with Python backend frameworks. In this article, we showed you how to interact with MongoDB and MySQL databases by using simple CRUD operations written in Python.
