Database access with Python
Python programming can be used in RDBMS applications. MySQL is a very popular RDBMS. In this topic, I have discussed how to connect to MYSQL using Python. The objective is to establish a database connection with MySQL and to access and manipulate MySQL tables. The MySQL version here is 8.0.15.
Python requires a MySQL driver to access the MySQL database. Here, I will use the driver “mysql-connector-python“. I recommend that you use PIP to install this driver. Type the following from your command line:
$ python -m pip install mysql-connector-python
Here ‘$’ indicates the prompt in your command line.
Several operations (like create database & table, delete table, insert into table, and select records from table etc.) can be performed on MySQL RDBMS using Python:~
1) Creation of a database:
To create a database in MySQL using Python we have to use different functions like connect(), cursor(), execute(), close() etc. which are described here with coding example. See the code below:
# create_database.py
import mysql.connector
# creating a connection object to MYSQL database
testdb = mysql.connector.connect(
user='root', # username is 'root'
password='system', # password is 'system'
host='localhost', # hostname or host IP address
auth_plugin='mysql_native_password' # forcing to use native plugin instead of SHA2 plugin
)
# creating a cursor object for executing SQL query on database
cursor = testdb.cursor()
# used for executing SQL query on database
cursor.execute("create database test_database")
# checking if a database exists by listing all databases in system
cursor.execute("show databases")
for db in cursor:
print(db)
# closing the database connection
testdb.close()
Output:
('information_schema',)
('mysql',)
('performance_schema',)
('sys',)
('test',)
('test_database',)
2) Creation of a table:
To create a table in MySQL, we use the “create table” statement. Ensure that you have provided the name of the database while creating the connection. You can check if a table exists by listing all the tables in your database using the “show tables” statement. To see the structure of a table, one can use the “describe” statement.
Later if you wish to change the structure of the table, you may do it using the “alter table” statement. All these statements can be executed using the execute() method. See the code below:
# create_table.py
import mysql.connector
# creating a connection object to a MYSQL database
testdb = mysql.connector.connect(
user='root', # username is 'root'
password='system', # password is 'system'
host='localhost', # hostname or host IP address
database='test_database', # database name
auth_plugin='mysql_native_password' # forcing to use native plugin instead of SHA2 plugin
)
# creating a cursor object for executing SQL query on database
cursor = testdb.cursor()
# creating a table
cursor.execute("create table students (roll int(10), name VARCHAR(50))")
# showing tables in a particular database
cursor.execute("show tables")
for x in cursor:
print(x)
cursor.execute("alter table students add column stream varchar(5)")
print('\n')
# describing a table
cursor.execute("describe students")
for x in cursor:
print(x)
# closing the database connection
testdb.close()
Output:
('students',)
('roll', 'int(11)', 'NO', 'PRI', None, 'auto_increment')
('name', 'varchar(20)', 'YES', '', None, '')
('stream', 'varchar(5)', 'YES', '', None, '')
3) Insert records into a table:
To insert a record into MySQL table, we use the “insert into” statement. This can be done using the execute() method normally. It is also easier to insert multiple records into a table, using the executemany() method. The second parameter of this method is a list of tuples, containing the record you want to insert. It is to be noted that we must use the commit() method to make the changes (especially for insert, delete and update operations), otherwise no changes are made to the table.
#insert_into_table.py
import mysql.connector
# creating a connection to MYSQL database
testdb = mysql.connector.connect(
user='root', # username is 'root'
password='system', # password is 'system'
host='localhost', # hostname or host IP address
database='test_database', # database name
auth_plugin='mysql_native_password' # forcing to use native plugin instead of SHA2 plugin
)
# creating a cursor object for executing SQL query on database
cursor = testdb.cursor()
# inserting a single record into the table
sql = "insert into students (name, stream) VALUES (%s, %s)"
val1 = ('Neera', 'EIE')
cursor.execute(sql, val1)
testdb.commit() # to make the changes, otherwise no changes are made to the table
print(cursor.rowcount, "record inserted.")
# inserting multiple records into the table
val2 = [
('Ramesh', 'CSE'),
('Yasin', 'IT'),
('Smith', 'ECE'),
('Rustam', 'EE'),
("Goutam", 'ME')
]
cursor.executemany(sql, val2)
testdb.commit() # to make the changes, otherwise no changes are made to the table
print(cursor.rowcount, "records inserted.")
# closing the database connection
testdb.close()
Output:
1 record inserted.
5 records inserted.
4) Select records from a table:
To select records from a table in MySQL, we can use the “select” statement. It is easier to limit the number of records returned from the SQL query, by using the “limit” keyword with this statement. See the example below:
#select_from_table.py
import mysql.connector
# creating a connection to MYSQL database
testdb = mysql.connector.connect(
user='root', # username is 'root'
password='system', # password is 'system'
host='localhost', # hostname or host IP address
database='test_database', # database name
auth_plugin='mysql_native_password' # forcing to use native plugin instead of SHA2 plugin
)
# creating a cursor object for executing SQL query on database
cursor = testdb.cursor()
# selecting records from a table
cursor.execute("select * from students")
result = cursor.fetchall() # fetches all rows from the table
for row in result:
print(row)
print('\n')
# selecting a single record
cursor.execute("select * from students where stream='CSE'")
result = cursor.fetchone() # fetches only one row
print(result)
print('\n')
# limiting no. of records from the table
cursor.execute("select * from students limit 3")
result = cursor.fetchall() # fetches all rows from the table
for row in result:
print(row)
# closing the database connection
testdb.close()
Output:
(1, 'Neera', 'EIE')
(2, 'Ramesh', 'CSE')
(3, 'Yasin', 'IT')
(4, 'Smith', 'ECE')
(5, 'Rustam', 'EE')
(6, 'Goutam', 'ME')
(2, 'Ramesh', 'CSE')
(1, 'Neera', 'EIE')
(2, 'Ramesh', 'CSE')
(3, 'Yasin', 'IT')
5) Delete records from a table:
To delete records from an existing table, we use the “select from” statement:
# delete_from_table.pyimport mysql.connector
# creating a connection to MYSQL database
testdb = mysql.connector.connect(
user='root', # username is 'root'
password='system', # password is 'system'
host='localhost', # hostname or host IP address
database='test_database', # database name
auth_plugin='mysql_native_password' # forcing to use native plugin instead of SHA2 plugin
)
# creating a cursor object for executing SQL query on database
cursor = testdb.cursor()
# deleting records from a table
cursor.execute("delete from students")
testdb.commit() # to make the changes to the table
print(cursor.rowcount, "record(s) deleted.")
# closing the database connection
testdb.close()
Output:
6 record(s) deleted.6) Drop a table:
We can drop an existing table by using the “drop table” statement. But, if the table is already dropped, or does not even exist, you can use the if exists keyword with this statement to prevent error. See the code below:
# drop_table.py
import mysql.connector
# creating a connection to MYSQL database
testdb = mysql.connector.connect(
user='root', # username is 'root'
password='system', # password is 'system'
host='localhost', # hostname or host IP address
database='test_database', # database name
auth_plugin='mysql_native_password' # forcing to use native plugin instead of SHA2 plugin
)
# creating a cursor object for executing SQL query on database
cursor = testdb.cursor()
# dropping a table
sql = "drop table if exists students"
cursor.execute(sql)
# closing the database connection
testdb.close()