Python Databases in Python
In most of the commercial applications that you will build in the future, you will inevitably deal with databases to store information in an orderly and organized manner.
There are many types of databases, and each type of them has a special module and is ready to deal with it.
In most of the commercial applications that you will build in the future, you will inevitably deal with databases to store information in an orderly and organized manner. There are many types of databases, and each type has its own module and is ready to work with it. In this lesson, you will learn how to interact with MySQL databases from your applications developed in Python.
In this lesson, you will learn how to work with databasesMySQLOne of your applications that you develop in Python.
Technical information
DatabasesMySQLIt is one of the most popular types of free databases used by developers, and for this reason it was adopted in the explanation.
We will also assume that you have already installed databasesMySQLon your device because in this lesson we will only explain how to deal with it.
note
In the event that you do not have any idea or experience in dealing with database rules, we have previously prepared a comprehensive reference to learn it from scratch.
The database course is on the blog
Installing the package mysql-connectorin Python
to work with databasesMySQLThe package mysql-connectorintended to handle it must be installed.
So open Command Prompt atPyCharmAnd write the following command.
pip install mysql-connector
After the package has been mysql-connectorsuccessfully installed, you will be able to include it and use the functions in it.
Connecting to databasesMySQLin python
The first thing you need to do to connect to the database is to include the module mysql.connectorfrom the package mysql-connectoras follows.
import mysql.connector
Now you can use functionsconnect(), cursor(), execute()And other ready-made functions in this module to deal with databases.
MySQL -for a functionconnect()
This function is used to create an object whose type MySQLConnectionallows us to connect to the database. It is built as follows.
mysql.connector.connect(host='', user='', passwd='', database='')
The parameter
hostwe pass to the location on which the database server is located.The parameter
userwe pass to the user name with which we will deal with the database.The parameter
passwdwe pass the password of the user with which we will interact with the database.The parameter
databasewe pass to the name of the database we intend to work with.
note
The default information for connecting to databasesMySQLIf you haven't changed it, the following are:
The default username is
'root'The default user does not have a password, which is his password
''If the database is on your computer, the parameter value
hostwill be'localhost'
In all the examples in which we will connect to databases, we will use this information to establish the connection.
In the following example we have included the packagemysql-connector,Then we call the function connect()to create an object that allows us to connect to the database server.
Then we rendered this object just to make sure that the object was created in memory.
Example
# connector Here we have included all the content of the module import mysql.connector # MySQL to return an object that allows us to connect to the connect databases here we called the function db = mysql.connector.connect( host='localhost', user='root', passwd='' ) # It is just to make sure that the connection information to the database server is correct because if it is not then a db error will appear here we have printed the object print(db)
• We will get a result similar to the following when we run the fileTest.
MySQL - Function cursor()
This function is called from the object returned by the functionconnect(),And it returns an object of its typeMySQLCursor.
objecttheMySQLCursorIt contains ready-made functions that can be used for the following reasons:
To send inquiries(Queries)to the database server.
to get results(Results)Returned by the database server after the query is executed.
Don't worry, you'll learn how to use it later from the examples.
MySQL - Function execute(operation)
This function is called from the object returned by the functioncursor(),In order to send queries to the database server.
Where the parameter is operationwe pass a text representing the query we want to send to the database server.
Don't worry, you'll learn how to use it later from the examples.
Comprehensive examples of working with databases in Python
From the following examples, you will learn how to create a simple database called(company),Then we will create a table to store employee data(employee).
We will assume that each employee has a name(name),have a phone number(phone),And he has a tariff number(id)special in it.
The examples are laid out in a coherent and precise way so that they teach you how to create a database, create tables in it, modify table fields, fill tables with data, modify table data, etc..
Database - How to create a new database in Python
The following example teaches you how to create a new database namedcompany.
Example
# connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to databases connect() Here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost'
)
# cursor To create a cursor() object here we called the function
cursor = db.cursor()
# and pass the query text to create a database for it to execute() here we called the function
cursor.execute('CREATE DATABASE company')
# Here we closed the connection with the database
cursor.close()
db.close()
• If you do not see any error after running the file Test, it means that the database has been created companysuccessfully.
How to create a table in the database in Python
The following example teaches you how to create a table named employeein the database companythat we created in the first example.
Example
# connector Here we have included all the content of the module
import mysql.connector
# 'company' to return an object that allows us to connect to the database connect() here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# It allows us to manipulate the cursor database to create a cursor() object here we called the function
cursor = db.cursor()
# in the database 'employee' and passing the query text which is to create a table named execute() here we called the function
cursor.execute('CREATE TABLE employee (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))')
# Here we closed the connection with the database
cursor.close()
db.close()
• If you do not see any error after running the file Test, it means that the table has been created employeesuccessfully in the databasecompany.
How to add a new column to a table in Python
The following example teaches you how to add a new column named phonein the table employeethat we created in the second example.
Example
# connector Here we have included all the content of the module
import mysql.connector
# 'company' to return an object that allows us to connect to the database connect() here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# It allows us to manipulate the cursor database to create a cursor() object here we called the function
cursor = db.cursor()
# 'employee' in table 'phone' and passing the query text to add a new column named execute() here we called the function
cursor.execute("ALTER TABLE employee ADD COLUMN phone VARCHAR(30)")
# Here we closed the connection with the database
cursor.close()
db.close()
• If you do not see any error after running the file Test, it means that a column whose name has been added phonesuccessfully in the tableemployee.
How to add a new line in a table in Python
The following example teaches you how to add a line(no record)In the table employeewe created in the previous examples.
The information that we will add on a line in the table, we will arrange in an orderly mannertuple.
Technical information
To save any information that you have added, modified or deleted from any table in the database, you must call the function commit()to save the changes.
Example
# mysql.connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to databases connect() Here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# It allows us to manipulate the cursor database to create a cursor() object here we called the function
cursor = db.cursor()
# 'employee' we put in the query text which allows adding a new line in the variable sql table
sql = 'INSERT INTO employee(name, phone) values (%s, %s)'
# represents the values to be placed in order in the tuple is the val object
val = ('Ahmad', '96101200155')
# sql and pass the query text stored in the variable execute() here we called the function
# val and then the values that will be combined with the query body that we have stored in the object
cursor.execute(sql, val)
# To save the changes made in the database commit() here we called the function
db.commit()
# Here we have printed the number of lines that were added in the table due to the query that was sent earlier
print(cursor.rowcount, 'record(s) inserted')
# Here we closed the connection with the database
cursor.close()
db.close()
• If you do not see any error after running the file Test, it means that a new line has been added in the tableemployee.
• The following sentence will also be printed in the console.
Technical information
Before sending the query to the database, the function execute()combines the query text and the values that we pass to it as follows.
INSERT INTO employee(name, phone) values ('Ahmad', '9610200155')
How to add multiple lines in a table in Python
The following example teaches you how to add multiple lines(records)In the table employeethat we created in the previous examples at once.
To pass a set of lines to the functionexecutemany(),We are going to create listeach element that has atuple.
Thus, we will prepare the information that will be stored in an orderly manner.
Technical information
To save any information that you have added, modified or deleted from any table in the database, you must call the function commit()to save the changes.
Example
# mysql.connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to databases connect() Here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# It allows us to manipulate the cursor database to create a cursor() object here we called the function
cursor = db.cursor()
# 'employee' we put in the query text which allows adding a new line in the variable sql table
sql = 'INSERT INTO employee(name, phone) values (%s, %s)'
# represents the lines and values to be placed in order in the tuple is the val object
val = [
('Rami', '96170188201'),
('Sara', '96103200155'),
('Rola', '96176554235'),
('John', '96178665711'),
('Nada', '96171004321')
]
# sql and pass the query text stored in the variable execute() here we called the function
# val and then the values that will be combined with the query body that we have stored in the object
cursor.executemany(sql, val)
# To save the changes made in the database commit() here we called the function
db.commit()
# Here we have printed the number of lines that were added in the table due to the query that was sent earlier
print(cursor.rowcount, 'record(s) inserted')
# which was automatically set to the last item we added ( id ) here we have printed the tariff number
print('Last row id is', cursor.lastrowid)
# Here we closed the connection with the database
cursor.close()
db.close()
• If you do not see any error after running the file Test, it means that 5 new lines have been added in the tableemployee.
• The following sentence will also be printed in the console.
Last row id is 6 --> The last row id we added is 6 because it is the sixth line in the table
Technical information
Before sending the query to the database, the function executemany()combines the query text and the values that we pass to it as follows.
INSERT INTO employee(name, phone) values ('Rami', '96170188201');
INSERT INTO employee(name, phone) values ('Sara', '96103200155');
INSERT INTO employee(name, phone) values ('Rola', '96176554235');
INSERT INTO employee(name, phone) values ('John', '96178665711');
INSERT INTO employee(name, phone) values ('Nada', '96171004321');
How to get all data stored in a table in Python
The following example teaches you how to fetch all the data stored in a table and display it as it is.
Note: We will fetch the information of all the employees we stored in the previous examples in the tableemployee.
first example
# connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to the connect databases here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# cursor To create a cursor() object here we called the function
cursor = db.cursor()
# 'employee' and passing the query body which is to fetch all the employee information stored in the table execute() here we called the function
cursor.execute('SELECT * FROM employee')
# To return all the information returned by the query fetchall() here we called the function
# result We then store all the information returned in the object
# A line in a tuple whose element is a list is the result of the object
result = cursor.fetchall()
# i.e. returns a new line from the table in each cycle .result is in the tuple in each cycle returns for here we created a loop
# phone and name , id : consists of three tuples. The line to be returned, we typed as is.. Note that it is a
for row in result:
print(row)
# Here we closed the connection with the database
cursor.close()
db.close()
• You will get the following result when you run the file Testif you store the same data that we entered in the previous examples.
(2, 'Rami', '96170188201')
(3, 'Sara', '96103200155')
(4, 'Rola', '96176554235')
(5, 'John' , '96178665711')
(6, 'Nada', '96171004321')
The following example teaches you how to select the columns you want to display based on a numbertheIndexfor each column.
Actually, since each line we get from the table will be tuplemade up of three elements, we will display these elements as we want them to.
second example
# connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to the connect databases here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# cursor To create a cursor() object here we called the function
cursor = db.cursor()
# 'employee' and passing the query body which is to fetch all the employee information stored in the table execute() here we called the function
cursor.execute('SELECT * FROM employee')
# To return all the information returned by the query fetchall() here we called the function
# result We then store all the information returned in the object
# A line in a tuple whose element is a list is the result of the object
result = cursor.fetchall()
# and then displays the three values in it in order: result is in the object tuple in each cycle returns for here we created a loop
for row in result:
print('Id:', row[0])
print('Name:', row[1])
print('Phone:', row[2])
print('-----------------')
# Here we closed the connection with the database
cursor.close()
db.close()
• You will get the following result when you run the file Testif you store the same data that we entered in the previous examples.
Name: Ahmad
Phone: 96101200155
------------------
Id: 2
Name: Rami
Phone: 96170188201
-------------- ----
Id: 3
Name: Sara
Phone: 96103200155
------------------
Id: 4
Name: Rola
Phone: 96176554235
---------- --------
Id: 5
Name: John
Phone: 96178665711
------------------
Id: 6
Name: Nada
Phone: 96171004321
------------------
The following example teaches you how to select the columns you want to display based on the names of the columns that actually exist in the table.
Here we will specify to the function cursor()that each line returned will bedict,Then automatically the name of each column in the table will be set as the key, and the values stored in the table as the key values.
third example
# connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to the connect databases here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# dict noting that we want to return any line fetched as a cursor object to create a cursor() object here we called the function
cursor = db.cursor(dictionary=True)
# 'employee' and passing the query body which is to fetch all the employee information stored in the table execute() here we called the function
cursor.execute('SELECT * FROM employee')
# To return all the information returned by the query fetchall() here we called the function
# result We then store all the information returned in the object
# A line in the table represents a dict whose each element is a list which is the result of the object
result = cursor.fetchall()
# result is in object dict every cycle returns for here we have created a loop
# row will be fetched from the phone object , key value name and key value id key value
for row in result:
print('ID:', row.get('id'))
print('Name:', row.get('name'))
print('Phone:', row.get('phone'))
print('-----------------')
# Here we closed the connection with the database
cursor.close()
db.close()
• You will get the following result when you run the file Testif you store the same data that we entered in the previous examples.
Name: Ahmad
Phone: 96101200155
------------------
Id: 2
Name: Rami
Phone: 96170188201
-------------- ----
Id: 3
Name: Sara
Phone: 96103200155
------------------
Id: 4
Name: Rola
Phone: 96176554235
---------- --------
Id: 5
Name: John
Phone: 96178665711
------------------
Id: 6
Name: Nada
Phone: 96171004321
------------------
How to get data stored in a table under specified conditions in Python
The following example teaches you how to fetch data stored in a table under specific conditions.
Note: We will fetch the information of each employee in the table that employeehas a number Idgreater than3 .
first example
# connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to the connect databases here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# cursor To create a cursor() object here we called the function
cursor = db.cursor()
# greater than 3 id that have the number 'employee' and pass the query body which is to fetch all the employee information stored in the table execute() here we called the function
cursor.execute('SELECT * FROM employee WHERE id > 3')
# To return all the information returned by the query fetchall() here we called the function
# result We then store all the information returned in the object
# A line in a tuple whose element is a list is the result of the object
result = cursor.fetchall()
# result is in the tuple in every cycle that returns for here we have created a loop
# It then displays it as .row and then stores it temporarily in the object
for row in result:
print(row)
# Here we closed the connection with the database
cursor.close()
db.close()
• You will get the following result when you run the file Testif you store the same data that we entered in the previous examples.
(5, 'John', '96178665711')
(6, 'Nada', '96171004321')
note
If you write a query that returns only one line, then it is better and easier for you to use the function fetchone()to handle the fetched data.
This function returns tupleone that contains the information returned.
And if you send a query that returns more than one line, and then use this function to deal with the returned data, it will only return the last line returned to you by the query.
The following example teaches you how to fetch a single line from the database and then display the fetched data as it is.
second example
# connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to the connect databases here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# cursor To create a cursor() object here we called the function
cursor = db.cursor()
# is equal to 1 id which has the number 'employee' and passes the query text which is to fetch all the employee information in the table execute() here we called the function
cursor.execute('SELECT * FROM employee WHERE id = 1')
# tuple so that all the information returned by the query returns as a fetchone() object here we called the function
# result We then store all the information returned in the object
result = cursor.fetchone()
# as result here we have displayed the information stored in the object
print(result)
# Here we closed the connection with the database
cursor.close()
db.close()
• You will get the following result when you run the file Testif you store the same data that we entered in the previous examples.
The following example teaches you how to select the columns you want to display based on a numbertheIndexfor each column.
Actually, since the line that we will get from the table will be tuplecomposed of three elements, we will display these elements as we want.
third example
# connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to the connect databases here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# cursor To create a cursor() object here we called the function
cursor = db.cursor()
# is equal to 1 id which has the number 'employee' and passes the query text which is to fetch all the employee information in the table execute() here we called the function
cursor.execute('SELECT * FROM employee WHERE id = 1')
# tuple so that all the information returned by the query returns as a fetchone() object here we called the function
# result We then store all the information returned in the object
result = cursor.fetchone()
# result Here we have displayed the information stored in the object
print('Id:', result[0])
print('Name:', result[1])
print('Phone:', result[2])
# Here we closed the connection with the database
cursor.close()
db.close()
• You will get the following result when you run the file Testif you store the same data that we entered in the previous examples.
Name: Ahmad
Phone: 96101200155
The following example teaches you how to select the columns you want to display based on the names of the columns that actually exist in the table.
Here we will specify to the function cursor()that the line to be returned will bedict,Then automatically the name of each column in the table will be set as the key, and the values stored in the table as the key values.
Fourth example
# connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to the connect databases here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# dict noting that we want to return any line fetched as a cursor object to create a cursor() object here we called the function
cursor = db.cursor(dictionary=True)
# is equal to 1 id which has the number 'employee' and passes the query text which is to fetch all the employee information in the table execute() here we called the function
cursor.execute('SELECT * FROM employee WHERE id = 1')
# tuple so that all the information returned by the query returns as a fetchone() object here we called the function
# result We then store all the information returned in the object
result = cursor.fetchone()
# result Here we have displayed the information stored in the object
print('ID:', result.get('id'))
print('Name:', result.get('name'))
print('Phone:', result.get('phone'))
# Here we closed the connection with the database
cursor.close()
db.close()
• You will get the following result when you run the file Testif you store the same data that we entered in the previous examples.
Name: Ahmad
Phone: 96101200155
How to modify data stored in a table in Python
The following example teaches you how to modify the information stored in a table.
Actually, we will change the name of the employee who has a number idequal to 3 in the tableemployee.
Technical information
To save any information that you have added, modified or deleted from any table in the database, you must call the function commit()to save the changes.
Example
# mysql.connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to databases connect() Here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# It allows us to manipulate the cursor database to create a cursor() object here we called the function
cursor = db.cursor()
# 'employee' we put the query text in which to modify the value of a field in the sql variable table
sql = 'UPDATE employee SET name = %s WHERE ID = %s'
# sql represents the information to be combined with the query that we have processed in the tuple is a val object
val = ('Lara', '3')
# sql and pass the query text stored in the variable execute() here we called the function
# val and then the values that will be combined with the query body that we have stored in the object
cursor.execute(sql, val)
# To save the changes made in the database commit() here we called the function
db.commit()
# Here we print the number of lines whose content has been modified in the table due to the query that was sent earlier
print(cursor.rowcount, 'record(s) affected')
# Here we closed the connection with the database
cursor.close()
db.close()
• If you do not see any error after running the file Test, it means that the name of the employee has been successfully modified in the tableemployee.
• The following sentence will also be printed in the console.
Technical information
Before sending the query to the database, the function execute()combines the query text and the values that we pass to it as follows.
UPDATE employee SET name = 'Lara' WHERE ID = 3
How to delete data stored in a table in Python
The following example teaches you how to delete a line from a table.
Actually, we will delete all employee information that has a number idequal to 4 in the tableemployee.
Technical information
To save any information that you have added, modified or deleted from any table in the database, you must call the function commit()to save the changes.
first example
# mysql.connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to databases connect() Here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# It allows us to manipulate the cursor database to create a cursor() object here we called the function
cursor = db.cursor()
# 'employee' we put in the query script that deletes one line from the variable sql table
sql = 'DELETE FROM employee WHERE ID = %s'
# sql represents the information to be combined with the query that we have processed in the tuple is a val object
val = ('4',)
# sql and pass the query text stored in the variable execute() here we called the function
# val and then the values that will be combined with the query body that we have stored in the object
cursor.execute(sql, val)
# To save the changes made in the database commit() here we called the function
db.commit()
# Here we have printed the number of lines that were deleted from the table due to the query that was sent earlier
print(cursor.rowcount, 'record(s) deleted')
# Here we closed the connection with the database
cursor.close()
db.close()
• If you do not see any error after running the file Test, it means that the line has been successfully deleted from the tableemployee.
• The number of lines that have been deleted will also be printed as follows.
Technical information
Before sending the query to the database, the function execute()combines the query text and the values that we pass to it as follows.
DELETE FROM employee WHERE ID = 4
The following example teaches you how to delete all the lines in the table and print the number of lines that have been deleted.
second example
# mysql.connector Here we have included all the content of the module
import mysql.connector
# MySQL to return an object that allows us to connect to databases connect() Here we called the function
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
# It allows us to manipulate the cursor database to create a cursor() object here we called the function
cursor = db.cursor()
# 'employee' and passing the query text which is to delete all the lines in the table execute() here we called the function
cursor.execute('DELETE FROM employee')
# To save the changes made in the database commit() here we called the function
db.commit()
# Here we have printed the number of lines that were deleted from the table due to the query that was sent earlier
print(cursor.rowcount, 'record(s) deleted')
# Here we closed the connection with the database
cursor.close()
db.close()
• If you do not see any error after running the file Test, it means that all lines of the table have been deleted employeesuccessfully.
• The number of lines that have been deleted will also be printed as follows.
How to deal with databases in the best possible way in Python
The following example teaches you how to deal with databases in the best possible way to ensure that no error occurs that causes the program to stop suddenly.
Actually, we will put every code that could cause an error inside a blocktry.. except.
Also, we will intentionally write a logical query that cannot be executed, as we will try to delete all employee information that has a number idequal to 1000 in the tableemployee.
Technical information
To save any information that you have added, modified or deleted from any table in the database, you must call the function commit()to save the changes.
In order to cancel the execution of any query you sent, you must call the function rollback()which will automatically cancel all the changes you made previously.
Example
# mysql.connector Here we have included all the content of the module
import mysql.connector
try:
# MySQL to return an object that allows us to connect to databases connect() Here we called the function
# Because there is a possibility that an error occurred while connecting to the database server try we called this function inside a block
db = mysql.connector.connect(
user='root',
passwd='',
host='localhost',
database='company'
)
except:
# exit(0) In the event of a communication error, the following print command will be executed and the program will stop working because of the function
print("Error: Can't connect to database")
exit(0)
# It allows us to manipulate the cursor database to create a cursor() object here we called the function
cursor = db.cursor()
# 'employee' we put in the query script that deletes one line from the variable sql table
sql = 'DELETE FROM employee WHERE ID = %s'
# sql represents the information to be combined with the query that we have processed in the tuple is a val object
val = ('1000',)
try:
# To save any changes made and commit() to send the query to the database. And we called the function execute(), here we called the function
# is equal to 1000 id because an error may occur if there is no employee who has the try number We put these two functions inside the block
cursor.execute(sql, val)
db.commit()
except:
# If an error occurs while executing the query, it will print that an error has occurred
# To cancel anything that happened in the database after trying to execute the query rollback() we call the function
print("Error: unable to delete a record")
db.rollback()
# Here we have printed the number of lines that were deleted from the table due to the query that was sent earlier
print(cursor.rowcount, 'record(s) deleted')
# Here we closed the connection with the database
cursor.close()
db.close()
• After running the file, Testan error will occur caused by the absence of an employee who has a number idequal to 1000 in the table, employeebut it will not lead to stopping the program and will not cause any problem in the database.
• The number of lines that have been deleted will also be printed as follows.