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

Preparations

OS Version

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

1
2
3
4
5
6
7
8
9
[email protected]:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 8.5 (jessie)
Release: 8.5
Codename: jessie
[email protected]:~$ uname -r
3.16.0-4-amd64
[email protected]:~$

Installing PyMySQL

Python版本使用3.4.2

1
2
3
4
5
6
[email protected]:~$ apt-cache search -n pymysql
python-pymysql - Pure-Python MySQL driver - Python 2.x
python3-pymysql - Pure-Python MySQL Driver - Python 3.x
[email protected]:~$ python3 --version
Python 3.4.2
[email protected]:~$

安裝python3-pymysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[email protected]:~$ 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) ...
[email protected]:~$

Using PyMySQL

Connection Test

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#導入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]))

執行結果

1
2
3
4
5
[email protected]:~$ 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)"));
[email protected]:~$

Check Version

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#!/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]))

執行結果

1
2
3
[email protected]:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
Database Version is 10.1.14-MariaDB-1~jessie
[email protected]:~$

Create Table

創建數據表city

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
#!/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]))

操作失敗結果

1
2
3
4
5
6
[email protected]:~$ 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;
[email protected]:~$

Insert Data

插入數據,需要執行commit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#!/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語句和執行語句也可改寫爲

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

cursor.execute(sql)

Select Data

讀取數據,可使用如下幾種方法

  • fetchall()
  • fetchone()
  • rowcount

Fetchall

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
#!/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]))

執行結果

1
2
3
4
[email protected]:~$ 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)
[email protected]:~$

Fetchone

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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]))

執行結果

1
2
3
4
5
6
7
8
9
[email protected]:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
(1, 'Shanghai', datetime.datetime(2016, 6, 28, 17, 7, 44), None)
[email protected]:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
(2, 'Hangzhou', datetime.datetime(2016, 6, 28, 17, 15, 29), None)
[email protected]:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
(1, 'Shanghai', datetime.datetime(2016, 6, 28, 17, 7, 44), None)
[email protected]:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
(2, 'Hangzhou', datetime.datetime(2016, 6, 28, 17, 15, 29), None)
[email protected]:~$

Rowcount

查詢返回數據條目數

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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]))

操作結果

1
2
3
[email protected]:~$ python3 ~/pythonLearning/PyMSQL/pymysqlConnection.py
2
[email protected]:~$

Update Data

更新數據,需要執行commit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
#!/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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
#!/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]))

執行結果

1
2
3
4
5
6
7
8
9
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