Python connects to MySQL database

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

1. PIP Installation
$ pip Install Pymysql

2. 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

######
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

Python connection MySQL

Python operations database, basic steps

1. Import the appropriate Python module
2. Connect to the database using the Connect function and return a connection object
3. Returns a Cursor object by using the cursor method of the Connection object
4. Execute SQL statements through the cursor object's Execute method
5. If you are executing a query statement, get the return result from the cursor object's Fetchall statement
6. Call the cursor object's Close method to close the cursor
7. Call the close side method of the connection object to close the database connection

Connect to MySQL Database

1. Create a test library
CREATE TABLE Employee (
First_Name Char (10),
Last_Name Char (10),
Age Int (10),
Sex char (10),
Income float
);

2. Create a remote connection authorization account
Mysql> Grant All on * * to [e-mail protected] '% ' identified by ' test123 ';
Query OK, 0 rows affected (0.14 sec)

mysql> flush Privileges;
Query OK, 0 rows affected (0.03 sec)

3. Scripting

#!/usr/bin/python3

Import Pymysql

db = Pymysql.connect ("192.168.100.150", "Test", "test123", "TestDB")

cursor = Db.cursor () # Creates a cursor object using the cursor () method cursor
Cursor.execute ("Select VERSION ()") executes SQL query #使用 Execute () method
data = Cursor.fetchone () # Gets a single piece of data using the Fetchone () method.
Print ("Database version: {0}". Format (data))
#print ("Database version:%s"% data)
Db.close ()

Perform
Python3 mysql_con.py
Database version:(' 5.6.40 ',)

Create a database table
Import Pymysql

db = Pymysql.connect ("192.168.100.150", "Test", "test123", "TestDB")
cursor = Db.cursor ()

Cursor.execute ("DROP TABLE IF EXISTS employee")
sql = "" "CREATE TABLE Employee (
First_Name Char (10),
Last_Name Char (10),
Age Int (10),
Sex char (10),
Income float) "" "

Cursor.execute (SQL)
Db.close ()

Database Insert Operations
Import Pymysql

db = Pymysql.connect ("192.168.100.150", "Test", "test123", "TestDB")
cursor = Db.cursor ()

sql = "" INSERT into employee (first_name,
Last_name,age,sex,income)
VALUES (' Rock ', ' Lin ', A, ' M ', 2000) ' ""

Try
Cursor.execute (SQL)
Db.commit () #提交数据库执行
Except
Db.rollback () #如果发生错误则回滚

Db.close ()

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:
Import Pymysql

db = Pymysql.connect ("192.168.100.150", "Test", "test123", "TestDB")

cursor = Db.cursor ()
Cursor.execute ("SELECT * FROM Employee")
data = Cursor.fetchall () #获取SQL语句所有记录
For user in data: #对返回的记录进行遍历
Print (user)
Db.close ()

Perform
[Email protected] db]# Python3 mysql_con.py
(' Rock ', ' Lin ', ' M ', 2000.0)
(' su ', ' Tian ', ' F ', 1000.0)

Database update operations
Import Pymysql

db = Pymysql.connect ("192.168.100.150", "Test", "test123", "TestDB")
cursor = Db.cursor ()

sql = "UPDATE Employee set age = Age + 1 Where sex = ' M '" #SQL更新操作

Try
Cursor.execute (SQL)
Db.commit ()
Except
Db.rollback ()

Db.close ()

Verify

Mysql> SELECT * from employee;
+------------+-----------+------+------+--------+
| first_name | last_name | Age | sex | Income |
+------------+-----------+------+------+--------+
| Rock | Lin | 21 | M | 2000 |

Database Delete operations
Import Pymysql

db = Pymysql.connect ("192.168.100.150", "Test", "test123", "TestDB")
cursor = Db.cursor ()

sql = "Delete from employee where age >" # SQL DELETE statement

Try
Cursor.execute (SQL)
Db.commit ()
Except
Db.rollback ()

Db.close ()

Verify
Mysql> SELECT * from employee;
Empty Set (0.00 sec)

Python connects to MySQL database