MySQL-Python – 4. Insert and Update in MySQL DB with mysql.connector

Posted on December 19, 2023December 21, 2023 by admin

This is a continuation from previous post MySQL-Python - 3.

In this page:

Insert

For insert sample, we will add a record in actor table from sakila sample database. We need to insert in actor table a record for person named NICK WALKEN which have last activity December 16, 2023

First let's see how records from actor table looks like. For this opening MySQL 8.0 Command Line Client there is:

After we see record structure we can imagine how SQL insert will looks like and maybe test this in MySQL 8.0 Command Line Client.

Insert is:

INSERT INTO actor (first_name, last_name, last_update) VALUES ('NICK', 'WALKEN',STR_TO_DATE('December 16, 2023', '%M %d,%Y'));

After run above select we verify result using a SELECT like below:

SELECT * FROM actor WHERE first_name LIKE 'NICK' AND last_name LIKE 'WALKEN';

And we will obviously delete it because we will run insert once verified in python code later. To delete:

DELETE FROM actor WHERE first_name LIKE 'NICK' AND last_name LIKE 'WALKEN';

Sample Code for INSERT

import datetime

import mysql.connector

connection = mysql.connector.connect(user='u2024', password='password1234', host='localhost', port=3306, database='sakila')

cursor = connection.cursor()

SQL_insert_actor = ("INSERT INTO actor (first_name, last_name, last_update)"

"VALUES (%s, %s, %s)")

values_for_actor = ('NICK','WALKEN', datetime.date(2023, 12, 16))

cursor.execute(SQL_insert_actor, values_for_actor)

# commit to the database

connection.commit()

cursor.close()

connection.close()

Below is the same code executed in PyCharm

Comments about code:

Flow is similar with select flow from "MySQL-Python - 3", i.e. there is connection object, cursor object, SQL statement this time is in string named SQL_insert_actor. In current code appear new commit() method, because having an insert that modify data, we need to commit to DB. Then follow known cursor and connection close.

Values that are used in insert are passed using notation similar with that from select with parameter, there is "VALUES (%s, %s, %s)".

To "populate" values, we put those in a tuple named values_for_actor using

values_for_actor = ('NICK','WALKEN', datetime.date(2023, 12, 16))

after that when run cursor execute:

cursor.execute(SQL_insert_actor, values_for_actor)

values from tuple i.e. 'NICK','WALKEN', datetime.date(2023, 12, 16) go one by one and will replace %s from SQL statement named SQL_insert_actor

Observation: when we checked with select statement actor table data, we see there column actor_id, this is not in VALUES because it is an AUTO_INCREMENT column.

Update

We propose to update NICK WALKEN record from actor table to change last_update to be December 19, 2023

Sample code for UPDATE:

import datetime

import mysql.connector

connection = mysql.connector.connect(user='u2024', password='password1234', host='localhost', port=3306, database='sakila')

cursor = connection.cursor()

SQL_update_actor = ("UPDATE actor SET last_update=%s WHERE first_name LIKE 'NICK' AND last_name LIKE 'WALKEN'")

tochange_last_update = (datetime.date(2023, 12, 19), )

cursor.execute(SQL_update_actor, tochange_last_update)

# commit to the database

connection.commit()

cursor.close()

connection.close()

We can check if update was ok, using select:

Comments about code:

- Code is similar with previous sample, main difference is SQL statement in this case is SQL_update_actor and the tuple tochange_last_update that we use to pass parameter to update, here tochange_last_update is a tuple with only one element, means this is why we not have (datetime.date(2023, 12, 19)) means heaving (datetime.date(2023, 12, 19), )

Comma from the end is for tuple with one element.

- Passing parameter to SQL statement, here SQL_update_actor is possible via %s, means something like replacing

SQL_update_actor = ("UPDATE actor SET last_update=%s WHERE first_name LIKE 'NICK' AND last_name LIKE 'WALKEN'")

tochange_last_update = (datetime.date(2023, 12, 19), )

cursor.execute(SQL_update_actor, tochange_last_update)

with

SQL_update_actor = ("UPDATE actor SET last_update=datetime.date(2023, 12, 19) WHERE first_name LIKE 'NICK' AND last_name LIKE 'WALKEN'")

cursor.execute(SQL_update_actor)

will not work. In this case datetime.date(2023, 12, 19) from inside SQL_update_actor will be treated like a routine from SQL point of view and _mysql_connector will give error

- parameter is expected to be tuple, list or dictionary

Means trying to use

tochange_last_update = datetime.date(2023, 12, 19)

(and not tochange_last_update = (datetime.date(2023, 12, 19), ) )

will fail with error:

mysql.connector.errors.ProgrammingError: Could not process parameters: date(2023-12-19), it must be of type list, tuple or dict

MySQL-Python – 3. Accessing MySQL DB with mysql.connector

Posted on December 13, 2023December 21, 2023 by admin

This is a continuation from MySQL-Python – 2 where we discussed about users and database.

In this page:

Installing mysql.connector

Using select with mysql.connector

Select with parameter

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

Python MySQL using select with parameter

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.

MySQL-Python – 2. Database and user

Posted on December 9, 2023December 21, 2023 by admin

This is continuation from MySQL-Python – 1 post where we installed MySQL server.

In this page:

Accessing database from MySQL Workbench

Accessing database using MySQL Command Line Client

Creating user from MySQL Command Line Client

Creating user from MySQL Workbench CE

Accessing database from MySQL Workbench

We will access Sakila sample database with Python.

First to remeber, when we installed MySQL we choose to install and configure "Samples and Examples". This option installed Sakila database.

We can see it fast, that it is on our server, either using in Windows 11: All Apps > MySQL > MySQL Workbench 8.0 CE

Click on Local instance

If password requested for root, type it, then MySQL Workbench will open and display Administration in Navigator panel.

Clik Schemas and we will see sakila db.

And clicking further on Tables we see those.

Accessing database using MySQL Command Line Client

Similar we can see sakila database using MySQL Command Line Client

In Windows 11: All Apps > MySQL > MySQL 8.0 Command Line Client

When prompt to enter password, enter root password.

Now we can use sakila database, for example type USE sakila;

then we can execute a sql command for example select how many lines are in actor table.

Observation: if sakila database was not installed initial when MySQL was installed

it can be added later, for example follow procedure like here.

In both cases from above user was root. In real life when access databases in applications we use a normal user (a user with fewer privileges, from security reason). Below is the procedure to create the user.

Creating user from MySQL Command Line Client

Lets suppose we need to create user u2024, after you login to MySQL Command Line tool like root run "SELECT user FROM mysql.user;" to check if user already exist.

u2024 is not in the list.

To create it run: CREATE USER 'u2024' IDENTIFIED BY 'password1234';

After create user we use again "SELECT user FROM mysql.user;" which confirm that user is created.

Next step is to grant privileges to u2024 in sakila database for this we run:

GRANT SELECT,CREATE,INSERT,DELETE,UPDATE,EXECUTE,ALTER,DROP,LOCK TABLES,CREATE TEMPORARY TABLES ON sakila.* TO 'u2024';

To test user, open a command prompt , change directory to folder where mysql is:

cd "C:\Program Files\MySQL1\MySQL Server 8.0\bin"

then connect with that user: mysql -u u2024 -p

Further we verify if we run commands, for example select.

Run quit; to exit from MySQL Command Line

Creating user from MySQL Workbench CE

Lets suppose we need to create user u2023.

After open MySQL Workbench CE and use root to connect we have this screen.

Click on "Users and Privileges" there is:

MySQL Workbench CE Users and Privileges 1

click "Add Account"

Write details as in underlined with red edit boxes, then clik Account Limits

Here we can set limits for user connections, updates and queries.

Click Administrative Roles.

here we can add administrative roles used for administer server. We not check something here because u2023 is just a user with limited privileges.

Click Schema Privileges.

To add privileges for sakila schema click "Add Entry..."

Here select schema sakila then click Ok.

Here select rights, similar with what we granted previous for u2024

Click Apply.

User u2023 is created.

MySQL-Python – 1. Installing mySQL

Posted on December 1, 2023December 22, 2023 by admin

In this page:

Downloading MySQL

MySQL install

MySQL configuration process

Downloading MySQL

Currently MySQL have 3 flavors:

MySQL Enterprise Edition

MySQL NDB Cluster CGE

MySQL Community (GPL)

In current article we will discuss how to install MySQL Community (GPL) edition on Windows 10 or 11.

To download installer first search for "MySQL download" or just load https://www.mysql.com/downloads/

Click on link "MySQL Community (GPL) Downloads"

Click on link "MySQL Installer for Windows"

Select mysql-installer-community-8.0.35.0.msi to download installer local on pc (or select mysql-installer-web-community-8.0.35.0.msi to start installation directly from web)

Specify location or path where to save file then click Save

MySQL install

After file mysql-installer-community-8.0.35.0.msi download finish, double click on it.

There are four setup type:

Server only

Client only

Full

Custom

We choose Custom setup because it permit to select components that we desire to install.

After we choose setup type we select the products to install.

Regarding to selected products it is desirable to mention that:

MySQL Workbench is a graphical tool which can be used for working with SQL commands.

MySQL Shell is a command prompt tool for the same, i.e. working with SQL commands, in plus in MySQL Shell we can use JavaScript and Python scripts.

Click next there is:

Then click Execute:

the install process start.

After install complete click Next

MySQL configuration process

It will follow configuration process.

Click Next

In this screen we can choose what king of server (computer) we will use, depending by the system resources (in special by RAM memory). As we see there are three choices:

Development Computer

Server Computer

Dedicated Computer

We will choose Server Computer.

In the next step we configure server connectivity.

MySQL server as most database servers support three kind of

connectivity:

TCP/IP

Named Pipe, this is a bidirectional IPC (interprocess communication) usual on Windows.

Shared Memory, this kind of connection use a shared memory like a communication chanel by which client and server can communicate.

We choos TCP/IP like connectivity, this is reliable. 3306 is the usual TCP/IP port which is used most of the cases.

There is also X protocol port 33060. A session object use this protocol to permit to run operation on the connected server.

Click Next permit to choose authentication method.

We choose strong password encryption.

Click Next.

mySQL configuration-root account password

In this screen we set root account password (remember it or note it). root user is the user with full privileges on server. In this screen using "Add User" account we can create new accounts.

Click Next.

Here we set MySQL to run as a Windows Service (option valid as we install on windows). Running as a services is useful due easy procedure to start/stop service.

We choose "Standard System Account" for run MySQL Service under a system account, which is more easy and safe.

Click Next.

mySQL configuration-server files permission-16

In "Server File Permissions" we choose "Yes, grant full access...", usual this is for easy configuration, but sometime is more suitable second option where we can review and decide which privileges to grant.

Click Next.