Cursor in Python
The cursor is an item that aids in query execution and records retrieval from databases. The cursor is crucial to the execution of the query. In-depth information on the execution techniques and how to use them in Python will be covered in this tutorial.
Through MySQL, we may build the cursor object.
Build the cursor object:
code:
#python py_mysql.py
#the library be imported
import mysql.connector
# generating connection
con = mysql.connector.connect(
host="localhost",
user="Annu",
password="Password"
)
#print out the connection
print(con)
# from the connection, import the cursor (con)
my_cursor = con.cursor()
#print out the my_cursor
print (my_cursor)output:
<mysql.connector.connection.MySQLConnection object at 0x000001B379405CF0>
MySQLCursor: (Nothing executed yet)Line 4: From MySQL, we import the connector class.
Lines 7 to 11: We use the connector class, which we have imported into our program, to access the connect method. We are currently supplying the connect method with our connection parameters. Depending on how you installed things, a separate username, as well as a password, will apply.
Line 16: We generated the cursor object and imported the cursor function from the connection (con) object (my_cursor).
Line 18: my_cursor, which we generated on line 16, is now printed in line 18, and the output reveals that MySQLCursor: (Nothing executed yet).
Function cursor.execute():
The execute () technique helps us to run the query and have the results match the query. The execute () function's syntax is as follows:
execute (query, args = None)
Parameters:
- Query: This ought to be a strings type.
- Arguments: By default, there are no arguments given because there are occasions when we can supply merely a query, such as a SELECT query that retrieves records without asking for any values. So that's the explanation behind the default value of args=None. However, the type of argument for an INSERT query must only be a tuple, list, or dict if we want to pass values.
Returns:
- The total number of rows impacted by the query will be returned.
Returned By:
- An integer will be the return type (int).
- We will now provide some samples.
Example 1: Only use the execute () function for queries.
# python py_mysql.py
# the library be imported
import mysql.connector
# generating connection
con = mysql.connector.connect (
host="localhost",
user="Annu",
password="Password",
database ="db_test"
)
# from the connection, import the cursor (con)
my_cursor = con.cursor()
my_cursor.execute ("SELECT * FROM STUDENT")
# iterate the outcome
for row in my_cursor:
print (row)
# Closing the con and cursor
my_cursor.close ()
con.close ()Output:
(1, 'Amit', 'IT')
(2, 'Anshu', 'Commerce')
(3, 'Sonali', 'Science')
(4, 'Rohit', 'Commerce')
(5, 'Arpit', 'IT')
(6, 'Reyansh', 'Science')Line 11: We expanded the database by one additional parameter name. Now, just this MySql database (db_test) will be attempted to connect to by our Python code.
Line 15: A cursor object was made (my_cursor).
Line 17: Using the execute function, we run the straightforward SELECT query.
Lines 20 to 21: As we iterated through the outcomes returned by the cursor object, we found that each record was returned as a tuple.
Example 2: For single record insertion, use the execute () function.
# python py_mysql.py
# the library be imported
import mysql.connector
# establishing a database connection
con = mysql.connector.connect(
host="localhost",
user="Annu",
password="Password",
database="db_test"
)
my_cursor = con.cursor()
# run the query using the value of their record.
query = 'INSERT INTO STUDENT (rollNo, Name, Subject) VALUES (%s, %s, %s)'
value = (8, "Ripunj", "Arts")
my_cursor.execute(query,value)
#We store the data in a table.
con.commit()
print (my_cursor.rowcount, "record(s) inserted.")Output:
1 record(s) inserted.
Now, see the data in the table
mysql> select * from student;
+--------+---------+----------+
| rollNo | Name | Subject |
+--------+---------+----------+
| 1 | Amit | IT |
| 2 | Anshu | Commerce |
| 3 | Sonali | Science |
| 4 | Rohit | Commerce |
| 5 | Arpit | IT |
| 6 | Reyansh | Science |
| 7 | Ripunj | Arts |
+--------+---------+----------+
7 rows in set (0.00 sec)
mysql>
Line 11: We expanded the database by one additional parameter name. Now, just this MySql database (db_test) will be attempted to connect to by our Python code.
Line 17: We formulate our insertion data query.
Line 18: To insert the value for such query into the table, we create it.
Line 21: We call the execute () method and provide it with the parameter's value and query.
The record is successfully entered into the table, as evidenced by the output mentioned above.
| Function | Function Details |
| fetchall() | Returns the whole result set or any remaining rows. |
| fetchone() | If there are no records to return, none will be returned instead of the single row from the result. |
| fetchmany([size]) | If there are no records to return, it will return[]. It will return the requested size of rows from the result sets. The initial size is one. |
Let's use an example to describe the aforementioned methods.
#python py_mysql.py
# the library be imported
import mysql.connector
# establishing connection
con = mysql.connector.connect(
host="localhost",
user="Annu",
password="Password",
database ="db_test"
)
# from the connection, import the cursor (con)
my_cursor = con.cursor()
my_cursor.execute("SELECT * FROM STUDENT")
# Fetch the 1st row
print (my_cursor.fetchone())
# Fetch the next four rows
print (my_cursor.fetchmany(4))
# Fetch all the remaining rows
print (my_cursor.fetchall())
# The result set is now empty
print (my_cursor.fetchmany())
# Closing the con and cursor
my_cursor.close()
con.close()
Output;
(1, 'Amit', 'IT')
[(2, 'Anshu', 'Commerce'), (3, 'Sonali', 'Science'), (4, 'Rohit', 'Commerce'), (5, 'Arpit', 'IT')]
[(6, 'Reyansh', 'Science'), (7, 'Ripunj', 'Atrs')]
[]
Line 20: The first record in the result sets is retrieved via the fetchone () method.
Line 22: The four records are retrieved from result sets by the fetchmany(4) function.
Line 24: All of the remaining records are retrieved from the result sets using the fetchall () method.
Line 26: The fetchmany () has a default size of 1, therefore it tries to get one record from result sets once more in line 26. But because the fetchmany (4) method and the fetchone () method have already accessed 4 and 1, respectively, it tries to retrieve the sixth tuple from record sets.
Function cursor.executemany():
In order to INSERT OR REPLACE numerous records at once, we can use the executemany () function. The executemany () function's syntax is as follows:
executemany (query, args)
Parameters:
- query: This ought to be a string type.
- Arguments: Since the arguments by default are None, we are unable to run the SELECT query in that. Values can be passed as either a list or a tuple.
Returns:
- If any rows were impacted by the query, it will return a count of those rows.
ReturnType:
- An integer will be used as the return type (int or None).
We'll now give a practical illustration of the aforementioned approach.
#python py_mysql.py
# the library be imported
import mysql.connector
# establishing a database connection
con = mysql.connector.connect(
host="localhost",
user="Annu",
password="Password",
database="db_test"
)
my_cursor = con.cursor()
# run the query using the value of their record.
query = 'INSERT INTO STUDENT (rollNo, Name, Subject) VALUES (%s, %s, %s)'
value = [(8, "Ankit", 'Arts'),
(9, "Sruti", 'Science'),
(10, "Mahima", "IT"),
(11, "Aryan", "IT")
]
my_cursor.executemany(query,value)
# We store the data in a table.
con.commit()
print (my_cursor.rowcount, "record(s) inserted.")Output:
4 record(s) inserted.
Now, see the data in the table
mysql> select * from student;
+--------+---------+----------+
| rollNo | Name | Subject |
+--------+---------+----------+
| 1 | Amit | IT |
| 2 | Anshu | Commerce |
| 3 | Sonali | Science |
| 4 | Rohit | Commerce |
| 5 | Arpit | IT |
| 6 | Reyansh | Science |
| 7 | Ripunj | Arts |
| 8 | Ankit | Arts |
| 9 | Sruti | Science |
| 10 | Mahima | IT |
| 11 | Aryan | IT |
+--------+---------+----------+
11 rows in set (0.00 sec)
mysql>
Line 17: We formulate our insertion data query.
Line 18: To insert values into the table, we generate a list of values.
Line 25: To input the many records, we are using the executemany () function.
Use the execute () function to run many queries at once:
The single execute () method also allows us to run numerous queries. One more parameter, multi, is accepted by the execute () function. Multi is set to False by default. If we maintain multi=True, we can use the semicolon (;) separator to run a multi-sql query.
# python py_mysql.py
# the library be imported
import mysql.connector
# establishing connection
con = mysql.connector.connect(
host="localhost",
user="Annu",
password="Password",
database ="db_test"
)
# from the connection, import the cursor (con)
my_cursor = con.cursor()
# a format parameter style query
query1 = "select * from student"
query2 = 'INSERT INTO STUDENT (rollNo, Name, subject) VALUES (%s, %s, %s)'
queries = [query1,query2]
value = (12, "Aashish", 'Science')
multi_results = my_cursor.execute(";".join(queries), value, multi=True)
count = 1
for rslt in multi_results:
# the outcome resembles a cursor,
# so that we have access to all of the cursor's properties
print ("query_{0} - {1} :".format(count, rslt.statement))
if rslt.with_rows:
for row in rslt:
print (row)
count = count + 1
else:
print ("No rslt")
print ()
my_cursor.close()
con.close()
Output:
query_1 - select * from student :
(1, 'Amit', 'IT')
(2, 'Anshu', 'Commerce')
(3, 'Sonali', 'Science')
(4, 'Rohit', 'Commerce')
(5, 'Arpit', 'IT')
(6, 'Reyansh', 'Science')
(7, 'Ripunj', 'Arts')
(8, 'Ankit', 'Arts')
(9, 'Sruti', 'Science')
(10, 'Mahima', 'IT')
(11, 'Aryan', 'IT')
query_2 - INSERT INTO STUDENT (rollNo, Name, subject) VALUES (12, 'Aashish', 'Science') :
No rslt
Line 23: Two inquiries were made into a list.
Line 28: We provide the execute () function with the list of queries, values, and separator (;). Additionally, we switch multi's value from False to True.
We can see our multi-query results in the report up top.
Conclusion
In this article, the execute () function was explored. Various execute () method types, such as the executemany () method, have also been seen. Additionally, we looked at querying through the cursor. Execute the method () with its various parameters. Finally, we have seen how to use the execute () technique for solving several queries.