python原生操作mysql
时间:2021-07-01 10:21:17
帮助过:7人阅读
import pymysql
2
3 HOST =
‘127.0.0.1‘
4 PORT = 3306
5 USER =
‘root‘
6 PASSWD =
‘123456‘
7 DB =
‘test‘
8 CHARSET =
‘utf8‘
9
10 conn =
pymysql.connect(
11 host=
HOST,
12 port=
PORT,
13 user=
USER,
14 passwd=
PASSWD,
15 db=
DB,
16 charset=
CHARSET
17 )
18
19 cursor =
conn.cursor()
20
21 create_dep =
"""CREATE TABLE department(
22 dep_id INT PRIMARY KEY,
23 dep_name VARCHAR(20) NOT NULL UNIQUE
24 )"""
25
26 create_emps =
"""CREATE TABLE employees(
27 emp_id INT PRIMARY KEY,
28 emp_name VARCHAR(20) NOT NULL UNIQUE,
29 birth_date DATE,
30 email VARCHAR(30),
31 dep_id INT,
32 FOREIGN KEY(dep_id) REFERENCES department(dep_id)
33 )"""
34
35 create_slary =
"""CREATE TABLE slary(
36 auto_id INT PRIMARY KEY,
37 date DATE,
38 basic INT,
39 awards INT,
40 emp_id INT,
41 FOREIGN KEY(emp_id) REFERENCES employees(emp_id)
42 )"""
43
44 # cursor.execute(create_dep)
45 # cursor.execute(create_emps)
46 # cursor.execute(create_slary)
47
48 # insert = ‘insert into department VALUES (%s,%s)‘
49 # cursor.execute(insert,(1,‘HR‘))
50 # cursor.execute(insert,(2,‘DEVOPS‘))
51 # cursor.execute(insert,(3,‘DEV‘))
52 # cursor.executemany(insert,[(4,‘OPS‘),(5,‘MANAGER‘)])
53
54
55
56 # select = ‘select * from department order by dep_id‘
57 # cursor.execute(select)
58 # result = cursor.fetchone()
59 # print(result)
60 # result = cursor.fetchmany(2)
61 # print(result)
62 # result = cursor.fetchall()
63 # print(result)
64
65 # select = ‘select * from department order by dep_id‘
66 # cursor.execute(select)
67 # cursor.scroll(4) # 默认以相对方式,从当前位置向下移动
68 # result = cursor.fetchone()
69 # print(result)
70 # cursor.scroll(0,mode=‘absolute‘) # absolute 一定是从开头移动
71 # result = cursor.fetchone()
72 # print(result)
73
74 #
75 # update1 = ‘update department set dep_name=%s where dep_name=%s‘
76 # cursor.execute(update1,(‘rs‘,‘HR‘))
77
78 delete1 =
‘delete from department where dep_id=%s‘
79 cursor.execute(delete1,(5
,))
80
81 conn.commit()
82
83
84
85 cursor.close()
86 conn.close()
87 if __name__ ==
"__main__":
88 pass
python原生操作mysql
标签:delete key unique value devops rtm for pytho res