PyMySQL installation

• Before using PyMySQL, we need to make sure PyMySQL is installed.
  PyMySQL download address: https://github.com/PyMySQL/PyMySQL.
• If it is not already installed, we can use the following command to install the latest version of PyMySQL:
  $ pip install PyMySQL
  If your system does not support the pip command, you can install it as follows:
  Use the git command to download the installation package (you can also download it manually):

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

Database connection

• Before connecting to the database, please confirm the following:
  1. Created the database testdb
  2. Create a table test
  3. testdb linked to database uses user name root and password 123456,
  4. The mysqldb module of python has been installed on the machine

• Example

mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb;
Database changed
mysql> create table testtable(id int ,username char(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into testtable values(20,'shan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into testtable values(40,'wu');
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtable;
+------+----------+
| id   | username |
+------+----------+
|   10 | shanwu   |
|   20 | shan     |
|   40 | wu       |
+------+----------+
3 rows in set (0.00 sec)

import pymysql

# Open database connection
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='testdb', charset='utf8')

# Create a cursor object cursor using the cursor() method
cursor = db.cursor()

# Executing SQL queries using the execute() method
cursor.execute("SELECT VERSION()")

# Use the fetchone() method to get a single piece of data
data = cursor.fetchone()

print("Database version : %s " % data)

# Close database connection
db.close()

#output
[root@shanwu python]# python3 db.py 
Database version : 5.6.36 

• Example of using class's method db connection

#!/usr/bin/env python
import pymysql

class TestMysql(object):
    def __init__(self):
        self.dbConfig = {
            "host": "127.0.0.1",
            "port": 3306,
            "user": "root",
            "passwd": "123456",
            "db": "testdb"
        }
        conn = pymysql.connect(**self.dbConfig)
        self.a = conn


    def select(self):
        print("select")

    def update(self):
        print("update")





if __name__ == '__main__':
    conn = TestMysql()