How We Can Run Mysql Query in Python

This article will show how we can do MySql query in python. To run the query in MySql, we first need to create a cursor object like a document structure that traverses the database.So let’s start to understand all concepts about the MySql query in python.

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.

pip install mysql-connector-python

If we are using any particular version, then we have to assign the version name as given below:

pip install mysql-connector-python==<insert_version_number_here>

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.

#python eastablish_connection.py

#import the library

import mysql.connector

# creating connection

conn = mysql.connector.connect(

host="localhost",

user="sammy",

password="password"

)

# print the conn

print(conn)

Output:

<mysql.connector.connection_cext.CMySQLConnection object at 0x7fbbf6291be0>

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.

#python create_a_database.py

#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:

<mysql.connector.connection_cext.CMySQLConnection object at 0x7f57cce9bbe0>

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.

➜ ~ mysql -u sammy -p

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:

#python conn_to_database.py

#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).

#python create_table.py

#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.

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.

#python record_insertion.py

#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.

mysql> select * from 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.

#python insert_record_multiple.py

#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:

mysql> select * from MOVIE;

+----+----------------+------+

| 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.

#python select_query.py

#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)]

(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:

https://github.com/shekharpandey89/mysql-query-connection

How We Can Run Mysql Query in Python

Method_1:

Method_2:

Connection Creation:

Output:

Line 4:

Line 7 to 11:

Line 13:

Create a Database:

Output:

Line 16:

Line 18:

Line 20:

Connection to the database:

Line 11:

Create a Table:

Line 14:

Line 15:

Line 18 to 21:

Line 24:

Add a record to the table:

Line 17:

Line 19:

Line 22:

Output:

Multiple record insertion:

Line 17:

Line 18:

Line 25:

Output:

Select record from the table :

Line 17 to 19:

Line 22:

Line 25 to 26:

Output:

Conclusion:

About the author

Shekhar Pandey