We can create the cursor object through the mysql.
Create a cursor object:
#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)
Output: python cursor_object.py
CMySQLCursor: (Nothing executed yet)
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 16: We imported the cursor method from the established connection (conn) object and created the cursor object (mycursor).
Line 18: Now, we just print this mycursor which we created on line 16, and the output shows that CMySQLCursor: (Nothing executed yet).
Method cursor.execute ():
The execute () method helps us to execute the query and return records according to the query. The syntax of the execute () function is:
Parameters :
- query: This should be a string type.
- Arguments: By default, the arguments are None because sometimes we can pass only a query like a SELECT query which fetches the records and does not require any values. So that’s the reason for the args=None by default. But if we want to pass the values in the case of the INSERT query, then the type of the arguments must be a tuple, list, or dict only.
Returns:
- It will return the count of the numbers of rows affected during the query.
Return Type:
- The return type will be an integer (int).
Now, we are going to show some examples.
Example_1: use execute () method only for query
#import the library
import mysql.connector
# creating connection
conn = mysql.connector.connect(
host="localhost",
user="sammy",
password="password",
database ="dbTest"
)
# import the cursor from the connection (conn)
mycursor = conn.cursor()
mycursor.execute("SELECT * FROM MOVIE")
# iterate over the result
for row in mycursor:
print(row)
# we close the cursor and conn both
mycursor.close()
conn.close()
Output: python simple_execute_function.py
(2, 'Kung Fu panda', 2014)
(3, 'Kung Fu panda', 2014)
(4, 'Frozen', 2014)
(5, 'Frozen2', 2020)
(6, 'Iron Man', 2013)
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.
Line 15: We created a cursor object (mycursor).
Line 17: We run a simple query SELECT through the execute function.
Line 20 to 21: We iterated over the results fetched by the cursor object and noticed that all records are returned in tuples.
Example_2: use execute () method for insertion single record
#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 = (7, "Merlin", 2001)
mycursor.execute(query,val)
# we commit(save) the records to the table
conn.commit()
print(mycursor.rowcount, "record(s) inserted.")
Output: python insert_record_execute.py
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> 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 |
| 7 | Merlin | 2001 |
+----+----------------+------+
7 ROWS IN SET (0.00 sec)
mysql>
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.
Line 17: We create our query for the insertion data.
Line 18: We create the value for that query to insert into the table.
Line 21: We use the execute () function and pass both query and val to them as a parameter.
And the above output shows that the record was successfully inserted into the table.
| Method | Method Details |
|---|---|
| fetchone() | This will return the single row from the result, and if there is no record to return, then it will return as None. |
| fetchmany([size]) | This will return the number of rows as size specified from the result sets, and if there is no record to return, then it will return []. The default size is 1. |
| fetchall() | Returns the all or remaining rows from the result set. |
Let’s explain the above methods using an example.
#import the library
import mysql.connector
# creating connection
conn = mysql.connector.connect(
host="localhost",
user="sammy",
password="password",
database ="dbTest"
)
# import the cursor from the connection (conn)
mycursor = conn.cursor()
mycursor.execute("SELECT * FROM MOVIE")
print(mycursor.fetchone()) # fetch the first row
print(mycursor.fetchmany(4)) # fetch the next 2 rows
print(mycursor.fetchall()) # fetch all the remaining rows
print(mycursor.fetchmany()) # the result set is now empty
# we close the cursor and conn both
mycursor.close()
conn.close()
Output: python cursor_method.py
[(2, 'Kung Fu panda', 2014), (3, 'Kung Fu panda', 2014), (4, 'Frozen', 2014), (5, 'Frozen2', 2020)]
[(6, 'Iron Man', 2013), (7, 'Merlin', 2001)]
[(6, 'Iron Man', 2013)]
Line 19: The fetchone () method fetches the first record from the result sets.
Line 20: The fetchmany(4) method fetches the four records from the result sets.
Line 21: The fetchall () method fetches all the remaining records from the result sets.
Line 22: The fetchmany () has default size 1, so it again tries to fetch one record from the result sets. But as the fetchmany (4) method already access the 4 records and fetchone () method access the 1st record, so it tries to fetch the 6th tuple from the record sets.
Method cursor.executemany():
The executemany () method helps us to INSERT OR REPLACE multiple records at once. The syntax of the executemany () function is:
Parameters :
- query: This should be a string type.
- Arguments: By default, the arguments are not None, So that’s why we are not able to execute the SELECT query in that. We can pass the values either type of tuple or list only.
Returns:
- It will return the count of the numbers of rows affected during the query if any.
Return Type:
- The return type will be an integer (int or None).
Now, we are going to show an example of the above 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: python executemany.py
+----+----------------+------+
| 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)
Execute many queries using a single execute () function:
We can also execute multiple queries using the single execute () function. The execute () function accepts one extra parameter, multi. The multi=False by default. If we keep multi=True, then we can execute a multi-sql query using the semicolon (;) separator.
#import the library
import mysql.connector
# creating connection
conn = mysql.connector.connect(
host="localhost",
user="sammy",
password="password",
database ="dbTest"
)
# import the cursor from the connection (conn)
mycursor = conn.cursor()
# query with format parameter style
query_1 = "select * from MOVIE"
query_2 = 'INSERT INTO MOVIE (id, name, year) VALUES (%s, %s, %s)'
queries = [query_1,query_2]
val = (8, "Series", 2001)
multiresults = mycursor.execute(";".join(queries), val, multi=True)
count = 1
for result in multiresults:
# result is just like a cursor, so we can access all
# attributes of the cursor
print("query_{0} - {1} :".format(count, result.statement))
if result.with_rows:
for row in result:
print(row)
count = count + 1
else:
print("No result")
print()
mycursor.close()
conn.close()
Output: python execute_multi.py
(1, 'Bruce Almighty', 2003)
(2, 'Kung Fu panda', 2014)
(3, 'Kung Fu panda', 2014)
(4, 'Frozen', 2014)
(5, 'Frozen2', 2020)
(6, 'Iron Man', 2013)
(7, 'Merlin', 2001)
query_2 - INSERT INTO MOVIE (id, name, YEAR) VALUES (8, 'Series', 2001) :
No RESULT
Line 23: We created a list of two queries.
Line 28: We pass the list of queries, val and separator (;) to the execute () method. We also change the value of multi from False to True.
In the above output, we can see our results of the multi-query.
Conclusion:
In this article, we studied the execute () method. We have also seen different types of execute () methods like the executemany () method. We also studied how to query through the cursor. Execute () method and different parameters of that method. At last, we have also seen how we can do multi-query using the execute () method.
The code for this article is available at the Github link:
https://github.com/shekharpandey89/cursor-execute-python
About the author
Shekhar Pandey