Python learning Day16 Python3 MySQL Database

Python3 MySQL Database

Python3 uses Pymysql to connect to the database, and to implement simple additions and deletions.

What is Pymysql?

Pymysql is a library used in the python3.x version to connect to the MySQL server, and MySQLdb is used in Python2.

Pymysql follows the Python database API v2.0 specification and includes the Pure-python MySQL client library.

Pymysql Installation

Before using Pymysql, we need to make sure that Pymysql is installed.

Pymysql:https://github.com/pymysql/pymysql.

If it is not installed, we can install the latest version of Pymysql using the following command:

$ pip Install Pymysql

If your system does not support the PIP command, you can install it in the following ways:

1. Download the installation package installation using the GIT command (you can also download it manually):

$ git clone https://github.com/PyMySQL/pymysql$ cd pymysql/$ python3 setup.py Install

2. If you need to make a version number, you can use the Curl command to install:

# x.x pymysql version number $ curl-l Https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz$ cd pymysql*#  Now you can delete pymysql* directory

Note: Make sure that you have root privileges to install the above modules.

The "Importerror:no module named setuptools" error message may appear during installation, meaning you do not have Setuptools installed and you can access https://pypi.python.org/pypi/ Setuptools find the installation method for each system.

Linux System Installation Example:

$ wget https://bootstrap.pypa.io/ez_setup.py$ python3 ez_setup.py

Database connection

Before you connect to a database, verify the following:

• You have created the database TESTDB.
• In the TestDB database, you have created table EMPLOYEE
• The Employee table fields are first_name, last_name, age, SEX, and INCOME.
• Connection database TestDB Use the user name "TestUser", the password is "test123", you can set your own or directly use the root user name and its password, MySQL database user authorization please use the GRANT command.
• The Python mysqldb module is already installed on your machine.

Instance:

The following example links the Mysql TESTDB database:

Instance (Python 3.0+)

#!/usr/bin/python3 ImportPymysql#Open a database connectiondb = Pymysql.connect ("localhost","testuser","test123","TESTDB" ) #Create a cursor object using the cursor () method cursorcursor =db.cursor ()#Execute SQL Query using the Execute () methodCursor.execute ("SELECT VERSION ()") #Use the Fetchone () method to get a single piece of data.data =Cursor.fetchone ()Print("Database version:%s"%data)#To close a database connectionDb.close ()

Execute the above script output as follows:

Database Version:5.5.20-log

Create a database table

If a database connection exists we can use the Execute () method to create a table for the database, creating table employee as follows:

Instance (Python 3.0+)

#!/usr/bin/python3 ImportPymysql#Open a database connectiondb = Pymysql.connect ("localhost","testuser","test123","TESTDB" ) #Create a Cursor object using the cursor () method cursorcursor =db.cursor ()#Execute SQL using the Execute () method, or delete if the table existsCursor.execute ("DROP TABLE IF EXISTS EMPLOYEE") #To create a table using Preprocessing statementssql ="""CREATE TABLE EMPLOYEE (first_name char () not NULL, last_name char (+), age INT, SEX CHAR (1), INCOME FLOAT)"""cursor.execute (SQL)#To close a database connectionDb.close ()

Database Insert Operations

The following instance inserts records into table EMPLOYEE using the Execute SQL INSERT statement:

Instance (Python 3.0+)

#!/usr/bin/python3 ImportPymysql#Open a database connectiondb = Pymysql.connect ("localhost","testuser","test123","TESTDB" ) #get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL INSERT statementsql ="""INSERT into EMPLOYEE (first_name, last_name, age, SEX, INCOME) VALUES (' Mac ', ' Mohan ', ' M ', ' 2000 ') 
    """Try:   #Execute SQL statementcursor.execute (SQL)#commit to database executionDb.commit ()except:   #Rollback If an error occursDb.rollback ()#To close a database connectionDb.close ()

The above example can also be written as follows:

Instance (Python 3.0+)

#!/usr/bin/python3 ImportPymysql#Open a database connectiondb = Pymysql.connect ("localhost","testuser","test123","TESTDB" ) #get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL INSERT statementsql ="INSERT into EMPLOYEE (first_name, last_name, age, SEX, INCOME) VALUES ('%s ', '%s ', '%d ', '%c ', '%d ') "%        ('Mac','Mohan', 20,'M', 2000)Try:   #Execute SQL statementcursor.execute (SQL)#Execute SQL statementDb.commit ()except:   #Roll Back when an error occursDb.rollback ()#To close a database connectionDb.close ()

The following code uses variables to pass parameters to the SQL statement:

" test123 "  "password"con.execute ('insert into Login ' values ('%s ', " %s ")' %              (user_id, password)) ...... ..... ..... ............

Database query Operations

Python queries MySQL uses the Fetchone () method to get a single piece of data, using the Fetchall () method to get multiple data.

• Fetchone (): This method gets the next query result set. The result set is an object
• Fetchall (): receives all the returned result rows.
• ROWCOUNT: This is a read-only property and returns the number of rows affected after the Execute () method is executed.

Instance:

Query all data with the salary (payroll) field greater than 1000 in the employee table:

Instance (Python 3.0+)

#!/usr/bin/python3 ImportPymysql#Open a database connectiondb = Pymysql.connect ("localhost","testuser","test123","TESTDB" ) #get an operation cursor using the cursor () methodcursor =db.cursor ()#SQL Query Statementssql ="SELECT * from EMPLOYEE WHERE INCOME > '%d '"% (1000)Try:   #Execute SQL statementcursor.execute (SQL)#get list of all recordsResults =Cursor.fetchall () forRowinchResults:fname=Row[0] lname= Row[1] Age= Row[2] Sex= Row[3] Income= Row[4]       #Print Results      Print("fname=%s,lname=%s,age=%d,sex=%s,income=%d"%(fname, lname, age, sex, income))except:   Print("error:unable to fetch data") #To close a database connectionDb.close ()

The results of the above script execution are as follows:

Fname=mac, Lname=mohan, age=20, Sex=m, income=2000

Database update operations

The update action is used to update the data in the data table, and the following instance modifies all the SEX fields in the TestDB table to ' M ', and the Age field increments by 1:

Python learning Day16 Python3 MySQL Database