This is a continuation from MySQL-Python – 2 where we discussed about users and database.
In this page:
Installing mysql.connector
First we need to install package mysql.connector, for this in command prompt we need to use pip.
We will use for new package install:
pip install mysql-connector-python
Or if page is already installed, is recommanded to upgrade it with:
pip install mysql-connector-python --upgrade
As we can see in this PC package is already installed.
Using select with mysql.connector
Sample code is below:
import mysql.connector
connection = mysql.connector.connect(user='u2024', password='password1234', host='localhost', port=3306, database='sakila')
cursor = connection.cursor()
SQLquery = "SELECT rental_id, customer_id, rental_date FROM rental "
cursor.execute(SQLquery)
for ( rental_id, customer_id, rental_date) in cursor:
print("Rental with id: {}, done by customer id: {} was rented on {:%d %b %Y}".format(customer_id, rental_id, rental_date))
cursor.close()
connection.close()
Comments about code:
First we create a connection object using method mysql.connector.connect which have like parameter user/password, MySQL host, MySQL port and database to which we will open connection.
Then we create a cursor object using cursor = connection.cursor(), in which we will fetch later, data from table.
To execute cursor first we need SQL statement, this is stored in a string named SQLquery
There is: SQLquery = "SELECT rental_id, customer_id, rental_date FROM rental "
Once we have SQL statement to fetch (or to get ) data in cursor we use execute method:
cursor.execute(SQLquery)
The for loop is used to iterate over cursor and to display lines (records) one by one.
Select with parameter
Code is similar, Below is sample code for this:
import mysql.connector
connection = mysql.connector.connect(user='u2024', password='password1234', host='localhost', port=3306, database='sakila')
cursor = connection.cursor()
SQLquery = ("SELECT rental_id, customer_id, rental_date FROM rental "
"WHERE rental_date BETWEEN %s AND %s")
rental_date = datetime.date(2005, 8, 22)
return_date = datetime.date(2005, 8, 25)
cursor.execute(SQLquery, ( rental_date, return_date))
for ( rental_id, customer_id, rental_date) in cursor:
print("Rental with id: {}, done by customer id: {} was rented on {:%d %b %Y}".format(customer_id, rental_id, rental_date))
cursor.close()
connection.close()
Comments about code:
SQLquery string is changed, for select with parameter(s) we added in WHERE clause BETWEEN %s AND %s, here "%s" instruct interpreter that for the parameter we will provide a value. Parameters in this case are rental_date and return_date value for those is like 'YYYY,MM,DD'. To provide date we use from datetime module date object datetime.date, this represent a naive date (it is a date that not contain information about time zone, daylight, etc., from simple reason we not need for this application this information).
In execute method we provide parameters rental_date, return_date now syntax is
cursor.execute(SQLquery, ( rental_date, return_date)) in this case rental_date will replace first "%s" and return_date by the second "%s" from discussed SQLquery.
The rest of the code is similar.