-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathmysqlDB.py
More file actions
78 lines (65 loc) · 2.04 KB
/
Copy pathmysqlDB.py
File metadata and controls
78 lines (65 loc) · 2.04 KB
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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
import contextlib
import traceback
import pymysql as mysql
host = "127.0.0.1"
port = "5306"
userName = "root"
passward = "root"
dataBaseName = "promotion"
# 定义上下文管理器，连接后自动关闭连接
@contextlib.contextmanager
def getMySqlConnection(host='127.0.0.1', port=5306, user='root', passwd='root', db='promotion', charset='utf8'):
'''获取数据库连接'''
conn = mysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)
cursor = conn.cursor(cursor=mysql.cursors.DictCursor)
try:
yield cursor
conn.commit()
finally:
print('关闭流')
cursor.close()
conn.close()
def getConnection():
'''获取msql连接'''
try:
con = mysql.connect(host=host, port=3306, user=userName, passwd=passward, db=dataBaseName, charset='utf8')
# con = mysql.connect(host, userName, passward, dataBaseName)
except Exception as e:
traceback.print_exc()
# raise RuntimeError("获取数据库连接失败")
return con
def getTeacherList():
'''获取歌手列表'''
con = getConnection()
# 获取游标
cur = con.cursor()
# cur.execute("select * from teacher")
# 参数化构造sql
cur.execute("select * from teacher where teacher_id=%s", ("1"))
con.commit()
# 获取所有数据
data = cur.fetchall()
'''
data = cur.fetchone()
print("获取一行数据:",data)
'''
for da in data:
print(da)
cur.close()
con.close()
def getUserList():
with getMySqlConnection() as cur:
cur.execute("select * from user where id =%s", "1")
data = cur.fetchall()
print("获取用户列表:", data)
def insertUser(data):
'''插入用户'''
with getMySqlConnection() as cursor:
cursor.execute("insert into user(id,name,sex) values(%s,%s,%s)", data)
new_id = cursor.lastrowid
print(new_id)
if __name__ == '__main__':
print(getTeacherList.__doc__)
# getTeacherList()
# getUserList()
insertUser(("4", "hello python", "1"))