PyMySQL是Python中用於連接MySQL的一個庫 GitHub地址,官方網站地址。以下是本人學習筆記,簡單實現數據的CRUD

Preparations

OS Version

操作系統爲Debian GNU/Linux 8.5 (jessie),內核版本3.16.0-4-amd64

lemp@LempStacker:~$ lsb_release -a
No LSB modules are available.
Distributor ID:	Debian
Description:	Debian GNU/Linux 8.5 (jessie)
Release:	8.5
Codename:	jessie
lemp@LempStacker:~$ uname -r
3.16.0-4-amd64
lemp@LempStacker:~$

Installing PyMySQL

Python版本使用3.4.2

lemp@LempStacker:~$ apt-cache search -n pymysql
python-pymysql - Pure-Python MySQL driver - Python 2.x
python3-pymysql - Pure-Python MySQL Driver - Python 3.x
lemp@LempStacker:~$ python3 --version
Python 3.4.2
lemp@LempStacker:~$

安裝python3-pymysql

lemp@LempStacker:~$ sudo apt-get install python3-pymysql
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Suggested packages:
  python-pymysql-doc
The following NEW packages will be installed:
  python3-pymysql
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 47.6 kB of archives.
After this operation, 271 kB of additional disk space will be used.
Get:1 http://mirrors.163.com/debian/ jessie-backports/main python3-pymysql all 0.6.2-1~bpo8+1 [47.6 kB]
Fetched 47.6 kB in 0s (202 kB/s)         
Selecting previously unselected package python3-pymysql.
(Reading database ... 173382 files and directories currently installed.)
Preparing to unpack .../python3-pymysql_0.6.2-1~bpo8+1_all.deb ...
Unpacking python3-pymysql (0.6.2-1~bpo8+1) ...
Setting up python3-pymysql (0.6.2-1~bpo8+1) ...
lemp@LempStacker:~$

Using PyMySQL

Connection Test

#導入PyMySQL庫
import pymysql as mariadb

#測試連接MariaDB數據庫
try:
    conn = mariadb.connect(
        host='localhost',
        user='lemp',
        passwd='lemp123',
        db='testdb1',
        charset='utf8'
    )
except Exception as e:
    print('Connection Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

執行結果

lemp@LempStacker:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
Connection Failed!
Error Code is 2003;
Error Content is Can't connect to MySQL server on 'localhost' ((1045, "Access denied for user 'lemp'@'localhost' (using password: YES)"));
lemp@LempStacker:~$

Check Version

#!/usr/bin/python3
# -*- utf8 -*-

#導入PyMySQL庫
import pymysql as mariadb

#測試連接MariaDB數據庫
try:
    conn = mariadb.connect(
        host='localhost',
        user='lemp',
        passwd='123454',
        db='testdb',
        charset='utf8'
    )

    #設置cursor
    cursor = conn.cursor()

    #撰寫SQL語句
    sql = "select version()"

    #執行SQL語句
    cursor.execute(sql)

    #獲取SQL語句返回內容
    #fetchall, fetchone
    data = cursor.fetchone()

    print('Database Version is %s' % data)

    #關閉cursor
    cursor.close()

    #關閉數據庫連接
    conn.close()

except Exception as e:
    print('Connection Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

執行結果

lemp@LempStacker:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
Database Version is 10.1.14-MariaDB-1~jessie
lemp@LempStacker:~$

Create Table

創建數據表city

#!/usr/bin/python3
# -*- utf8 -*-

#導入PyMySQL庫
import pymysql as mariadb

#測試連接MariaDB數據庫
try:
    conn = mariadb.connect(
        host='localhost',
        user='lemp',
        passwd='12345',
        db='testdb',
        charset='utf8'
    )

    #設置cursor
    cursor = conn.cursor()

    #撰寫SQL語句
    sql = """
        create table if not exists city(
            id int unsigned not null auto_increment primary key comment 'city primary id',
            name varchar(60) not null comment 'city name',
            create_time timestamp default current_timestamp comment 'data creat time',
            update_time timestamp null on update current_timestamp comment 'date modify time'
        )engine=innodb default charset=utf8 collate=utf8_general_ci;
    """

    try:
        cursor.execute(sql)
    except Exception as e:
        print('Execute Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

    #關閉cursor
    cursor.close()

    #關閉數據庫連接
    conn.close()

except Exception as e:
    print('Connection Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

操作失敗結果

lemp@LempStacker:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
Execute Failed!
Error Code is 1064;
Error Content is You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'timestampe default current_timestamp comment 'data creat time',
            upda' at line 4;
lemp@LempStacker:~$

Insert Data

插入數據,需要執行commit

#!/usr/bin/python3
# -*- utf8 -*-

#導入PyMySQL庫
import pymysql as mariadb

#測試連接MariaDB數據庫
try:
    conn = mariadb.connect(
        host='localhost',
        user='lemp',
        passwd='12345',
        db='testdb',
        charset='utf8'
    )

    #設置cursor
    cursor = conn.cursor()

    #撰寫SQL語句
    sql = "insert into city (name) values (%s)"

    try:
        cursor.execute(sql,('Shanghai'))

        #執行commit提交
        conn.commit()
    except Exception as e:
        #回滾
        conn.rollback()

        print('Execute Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

    #關閉cursor
    cursor.close()

    #關閉數據庫連接
    conn.close()

except Exception as e:
    print('Connection Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

SQL語句和執行語句也可改寫爲

#此處的%s必須用引號包裹起來
sql = "insert into city (name) values ('%s')" % ('Shanghai')

cursor.execute(sql)

Select Data

讀取數據,可使用如下幾種方法 * fetchall() * fetchone() * rowcount

Fetchall

#!/usr/bin/python3
# -*- utf8 -*-

#導入PyMySQL庫
import pymysql as mariadb

#測試連接MariaDB數據庫
try:
    conn = mariadb.connect(
        host='localhost',
        user='lemp',
        passwd='12345',
        db='testdb',
        charset='utf8'
    )

    #設置cursor
    cursor = conn.cursor()

    #撰寫SQL語句
    sql = "select * from city;"

    try:
        cursor.execute(sql)

        results = cursor.fetchall()

        for item in results:
            cityId = item[0]
            name = item[1]
            addTime = item[2]
            print(item)

    except Exception as e:
        print('Execute Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

    #關閉cursor
    cursor.close()

    #關閉數據庫連接
    conn.close()

except Exception as e:
    print('Connection Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

執行結果

lemp@LempStacker:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
(1, 'Shanghai', datetime.datetime(2016, 6, 28, 17, 7, 44), None)
(2, 'Hangzhou', datetime.datetime(2016, 6, 28, 17, 15, 29), None)
lemp@LempStacker:~$

Fetchone

sql = "select * from city order by rand() limit 1;"

try:
    cursor.execute(sql)

    results = cursor.fetchone()

    print(results)

    # for item in results:
    #     cityId = item[0]
    #     name = item[1]
    #     addTime = item[2]
    #     print(item)

except Exception as e:
    print('Execute Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

執行結果

lemp@LempStacker:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
(1, 'Shanghai', datetime.datetime(2016, 6, 28, 17, 7, 44), None)
lemp@LempStacker:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
(2, 'Hangzhou', datetime.datetime(2016, 6, 28, 17, 15, 29), None)
lemp@LempStacker:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
(1, 'Shanghai', datetime.datetime(2016, 6, 28, 17, 7, 44), None)
lemp@LempStacker:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
(2, 'Hangzhou', datetime.datetime(2016, 6, 28, 17, 15, 29), None)
lemp@LempStacker:~$

Rowcount

查詢返回數據條目數

sql = "select * from city;"

try:
    cursor.execute(sql)

    results = cursor.rowcount

    print(results)

    # for item in results:
    #     cityId = item[0]
    #     name = item[1]
    #     addTime = item[2]
    #     print(item)

except Exception as e:
    print('Execute Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

操作結果

lemp@LempStacker:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
2
lemp@LempStacker:~$

Update Data

更新數據,需要執行commit

#!/usr/bin/python3
# -*- utf8 -*-

#導入PyMySQL庫
import pymysql as mariadb

#測試連接MariaDB數據庫
try:
    conn = mariadb.connect(
        host='localhost',
        user='lemp',
        passwd='12345',
        db='testdb',
        charset='utf8'
    )

    #設置cursor
    cursor = conn.cursor()

    #撰寫SQL語句
    sql = "update city set name='%s' where id=1;" % ('ShanghaiTan')

    try:
        cursor.execute(sql)

        #執行commit提交
        conn.commit()

    except Exception as e:
        #回滾
        conn.rollback()

        print('Execute Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

    #關閉cursor
    cursor.close()

    #關閉數據庫連接
    conn.close()

except Exception as e:
    print('Connection Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

Delete Data

刪除數據,需要執行commit

#!/usr/bin/python3
# -*- utf8 -*-

#導入PyMySQL庫
import pymysql as mariadb

#測試連接MariaDB數據庫
try:
    conn = mariadb.connect(
        host='localhost',
        user='lemp',
        passwd='12345',
        db='testdb',
        charset='utf8'
    )

    #設置cursor
    cursor = conn.cursor()

    #撰寫SQL語句
    sql = "delete from city where id='%d'" % (1)

    try:
        cursor.execute(sql)

        #執行commit提交
        conn.commit()

    except Exception as e:
        #回滾
        conn.rollback()

        print('Execute Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

    #關閉cursor
    cursor.close()

    #關閉數據庫連接
    conn.close()

except Exception as e:
    print('Connection Failed!\nError Code is %s;\nError Content is %s;' % (e.args[0],e.args[1]))

執行結果

MariaDB [testdb]> select * from city\G
*************************** 1. row ***************************
         id: 2
       name: Hangzhou
create_time: 2016-06-28 17:15:29
update_time: NULL
1 row in set (0.00 sec)

MariaDB [testdb]>

Questions

疑問

  1. 插入數據後,如何獲取新插入的數據,使用LAST_INSERT_ID()
  2. SQL語句執行成功後,是否會返回反饋信息,如果是,能否獲取?

References


Change Log

  • 2016.06.28 17:38 Tue Asia/Shanghai
    • 初稿完成

  • Note Time: 2016.06.28 17:38 Tue
  • Note Location: Asia/Shanghai
  • Writer: lempstacker