Using mysql module of python

Keywords: Database MySQL Python github

PyMySQL installation

  • Before using PyMySQL, we need to make sure PyMySQL is installed.
    PyMySQL download address:
  • 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
$ cd PyMySQL/
$ python3 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='', 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

[root@shanwu python]# python3 
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": "",
            "port": 3306,
            "user": "root",
            "passwd": "123456",
            "db": "testdb"
        conn = pymysql.connect(**self.dbConfig)
        self.a = conn

    def select(self):

    def update(self):

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

Posted by ganlal on Thu, 19 Mar 2020 09:10:11 -0700