Before start, we have to install the MySql connector in our local system environment.
There are two methods to install:
Method_1:
We can directly download and install the MySQL connector from their official website according to our OS compatibility and version.
Method_2:
The easy way is to use the pip method.
If we are using any particular version, then we have to assign the version name as given below:
Connection Creation:
Now, in the first step after the successful installation of the MySql to our local system, we try to create a connection. If the connection is successful, then we can move to the next step.
#import the library
import mysql.connector
# creating connection
conn = mysql.connector.connect(
host="localhost",
user="sammy",
password="password"
)
# print the conn
print(conn)
Output:
Line 4:
We import the connector class from MySql.
Line 7 to 11:
We access the connect method through the connector class, which we already import into our program. Now, we are passing our connection parameters to the connect method. The user name and password will be different according to your installation process.
Line 13:
At last, we just print the connection, and it shows in the output connection done to the MySql, and it returns an object with its memory address.
Create a Database:
Now, we create a database with the help of python.
#import the library
import mysql.connector
# creating connection
conn = mysql.connector.connect(
host="localhost",
user="sammy",
password="password"
)
#print the connection
print(conn)
# import the cursor from the connection (conn)
mycursor = conn.cursor()
#print the mycursor
print(mycursor)
mycursor.execute("CREATE DATABASE dbTest")
Output:
CMySQLCursor: (Nothing executed yet)
Line 16:
We import the cursor method from the established connection (conn) object.
Line 18:
Now, we just print this mycursor which we created on line 16, and the output shows that CMySQLCursor: (Nothing executed yet).
Line 20:
We are executing a query to create a database, but it does not return anything. So if we print (mycursor.execute(“CREATE DATABASE dbTest”)), we will get None as a return type.
If we check our MySQL database, we can see that our new database (dbTest) is created successfully.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dbTest |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
Connection to the database:
Now, we are trying to connect with our newly created database (dbTest) with Python. The code for this is given below:
#import the library
import mysql.connector
# creating connection to the database
conn = mysql.connector.connect(
host="localhost",
user="sammy",
password="password",
database="dbTest"
)
# print the conn
print(conn)
Line 11:
We added one more parameter name to the database. Now, our python code will try to connect with this MySql database (dbTest) only.
Create a Table:
- Let’s create a new table (MOVIE) in the newly created database (dbTest).
- The three columns which we are going to use are id, name, and year. The ID and year will be INT (integer) type, and the name will be varchar type. We will also define a column (id) as a PRIMARY KEY.
- The column name will store the maximum number of characters 30, as we define varchar (30).
#import the library
import mysql.connector
# creating connection to the database
conn = mysql.connector.connect(
host="localhost",
user="sammy",
password="password",
database="dbTest"
)
# we create a mycursor object using the conn.cursor()
mycursor = conn.cursor()
mycursor.execute("DROP TABLE IF EXISTS MOVIE")
# we write a query to create a table
query = "CREATE TABLE MOVIE(id INT PRIMARY KEY,name varchar(30),year INT)"
# We execute the query here
mycursor.execute(query)
# after done the process, we close the connection
conn.close()
Line 14:
We create an object of the cursor.
Line 15:
We are also running a query here with the execute method that if the table name (MOVIE) already exists in the MySql database, it will delete that table. Otherwise, we will get the error that already exists.
Line 18 to 21:
We create a query to create a table and execute that query in line number 21 with the execute function.
Line 24:
At last, we close our connection.
Below is the MySql output, where we confirm that our table is successfully created inside of the MySql database.
+--------------------+
| Database |
+--------------------+
| dbTest |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use dbTest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_dbTest |
+------------------+
| MOVIE |
+------------------+
1 row in set (0.00 sec)
mysql> select * from MOVIE;
Empty set (0.00 sec)
mysql> select * from movie;
ERROR 1146 (42S02): Table 'dbTest.movie' doesn't exist
mysql> desc MOVIE;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| year | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
Add a record to the table:
Now, we are going to insert one record into the table. The python code for that is given below.
#import the library
import mysql.connector
# creating connection to the database
conn = mysql.connector.connect(
host="localhost",
user="sammy",
password="password",
database="dbTest"
)
mycursor = conn.cursor()
# execute the query with their record value
query = 'insert into MOVIE(id, name, year) \
values (1, "Bruce Almighty", 2003 )'
mycursor.execute(query)
# we commit(save) the records to the table
conn.commit()
Line 17:
We create a query to insert a record into the table MOVIE.
Line 19:
We execute that query.
Line 22:
We at last commit the record.
Output:
The below output shows that we have successfully record inserted into the table MOVIE.
+----+----------------+------+
| id | name | year |
+----+----------------+------+
| 1 | Bruce Almighty | 2003 |
+----+----------------+------+
1 row in set (0.00 sec)
mysql>
Multiple record insertion:
We can also add multiple records at once into the table. So, in this program, we are going to see that method. To enter the multiple records, we have to use the executemany () method instead of the execute () method.
#import the library
import mysql.connector
# creating connection to the database
conn = mysql.connector.connect(
host="localhost",
user="sammy",
password="password",
database="dbTest"
)
mycursor = conn.cursor()
# execute the query with their record value
query = 'INSERT INTO MOVIE (id, name, year) VALUES (%s, %s, %s)'
val = [(2, "Kung Fu panda", 2014),
(4, "Frozen", 2014),
(5, "Frozen2", 2020),
(6, "Iron Man", 2013)
]
mycursor.executemany(query,val)
# we commit(save) the records to the table
conn.commit()
print(mycursor.rowcount, "record(s) inserted.")
Line 17:
We create our query for the insertion data.
Line 18:
We create a list of values that we want to insert into the table.
Line 25:
We use the executemany () method to enter the multiple records.
Output:
+----+----------------+------+
| id | name | year |
+----+----------------+------+
| 1 | Bruce Almighty | 2003 |
| 2 | Kung Fu panda | 2014 |
| 3 | Kung Fu panda | 2014 |
| 4 | Frozen | 2014 |
| 5 | Frozen2 | 2020 |
| 6 | Iron Man | 2013 |
+----+----------------+------+
6 rows in set (0.00 sec)
Select record from the table :
In this program, we will add another query select to fetch the records from the table.
#import the library
import mysql.connector
# creating connection to the database
conn = mysql.connector.connect(
host="localhost",
user="sammy",
password="password",
database="dbTest"
)
mycursor = conn.cursor()
# execute the query and fetch all the records
query = 'SELECT * FROM MOVIE'
mycursor.execute(query)
result = mycursor.fetchall()
# we print our result
print(result)
# now, we do iteration on each record and print
for record in result:
print(record)
Line 17 to 19:
We create a select query and execute that query. The fetchall () method is used to fetch all the records from that particular table.
Line 22:
We print the result and find that all the records are tuple and inside a list. The below output is showing the list.
Line 25 to 26:
We iterate the list and print each tuple record.
Output:
(1, 'Bruce Almighty', 2003)
(2, 'Kung Fu panda', 2014)
(3, 'Kung Fu panda', 2014)
(4, 'Frozen', 2014)
(5, 'Frozen2', 2020)
(6, 'Iron Man', 2013)
Conclusion:
In this article, we have seen how to create a connection with MySql using python. We also studied the different methods of data insertion, like single or multiple data records insertions into the table. We have also seen how we can execute the query through python. We have to create a query and then pass that query to the execute () method for the execution and store that results to some variable.
The code for this article is available at the Github link:
