Python Data Persistence – Using pymysql Module

Python Data Persistence – Using pymysql Module

To make a Python program interact with a MySQL database, we need to install a DB-API compliant module. As mentioned earlier in this chapter, there are many alternatives available for this purpose. In this section, we shall discuss the use of pymysql module. In any case, the functionality of any DB-API compatible module is more or less similar, with a few differences.

The pymysql module is not a part of Python’s standard library. Hence, we have to install it using pip utility.

E:\python37>pip3 install pymysql

As per the DB-API standards, the first step is to establish a connection with the database to be used. Usage of connecting () function in pymysql module is a little different. Remember that MySQL databases are hosted on a server. Hence, the server’s URL and login credentials (user ID and password) must be passed to connect () function. Additionally, if you are trying to connect to an existing database, its name should also be provided. If you are going to create a new database (or use an existing database later), you needn’t provide its name in the connect ( ) function’s parameter list and just connect to the server.

Example

>>> import pymysql
>>> con=pymysql . connect ( ' localhost ' , 'root' , '***' )

MySQL provides the ‘CREATE DATABASE’ statement to start a new database. Execute this statement through the cursor object obtained from the connection object.

Example

>>> cur=con.cursor ( )
>>> cur.execute('create database mynewdb')

You can now start using this (or any other existing database) either by select_db () method or executing the ‘USE DATABASE’ statement.

Example

>>> con.select_db('mynewdb')
>>> #or
>>> cur.execute('use mynewdb')

Now that the new database has been created and is in use, you are now in a position to create a table and perform insert, update, delete and select operations on it exactly as we did on an SQLite database. The only thing you need to take into account is MySQL data types which are different from SQLite data types. (Table 8,2)

Integer types TINYINT, SMALLINT, MEDIUMINT, INTEGER, BIGINT
Float types FLOAT, DOUBLE , DECIMAL, NUMERIC
String types VARCHAR, TEXT, BLOB, CHAR, NCHAR
Date/time types DATE , TIME, DATETIME
Binary types BLOB, LONGBLOB

Example

>>> qry=' ' '
CREATE TABLE Products (
ProductID INTEGER PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR (20),
Price INTEGER
)
' ' '
>>> cur.execute(qry)

You can follow the process, as detailed in previous sections of this chapter, for insert, delete, and select operations.

Leave a Comment