Python基础-数据库相关操作

介绍python操作数据库相关操作，主要用sqlite和mysql

sqlite 创建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import sqlite3
print(sqlite3.apilevel)
print(sqlite3.paramstyle)
print(sqlite3.threadsafety)


conn = sqlite3.connect('first.db')
c = conn.cursor()
c.execute('''create table user_tb(
	_id integer primary key autoincrement,
	name text,
	pass text,
	gender text)''')
''
c.execute("""create table order_tb(
	_id integer primary key autoincrement,
	item_name text,
	item_price real,
	item_number real,
	user_id integer,
	foreign key(user_id) references user_tb(_id) )""")

c.close()
conn.close()
sqlite DML 相关操作
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
import sqlite3


# conn = sqlite3.connect('first.db')
# c = conn.cursor()
# c.execute("insert into user_tb values(null,?,?,?)",('孙悟空','123456','male'))
# c.execute('insert into order_tb values(null,?,?,?,?)',('鼠标','34.2','3','1'))
# conn.commit()
# c.close()
# conn.close()


# conn = sqlite3.connect('first.db')
# c = conn.cursor()
# c.executemany('insert into user_tb values(null,?,?,?)',
#               (('sun', '123456', 'male'),
#                ('bai', '123456', 'female'),
#                ('zhu', '123456', 'male'),
#                ('niu', '123456', 'male'),
#                ('tang', '123456', 'male')))
# conn.commit()
# c.close()
# conn.close()


# conn = sqlite3.connect('first.db')
# c = conn.cursor()
# c.executemany('update user_tb set name=? where _id=?',
#               (('小孙', 2),
#                ('小白', 3),
#                ('小猪', 4),
#                ('小牛', 5),
#                ('小唐', 6)))
# print('修改的行数:', c.rowcount)
# conn.commit()
# c.close()
# conn.close()


# conn = sqlite3.connect('first.db')
# c = conn.cursor()
# c.execute('select * from user_tb where _id>?', (2,))
# print('查询结果行数：', c.rowcount)
# for col in c.description:
#     print(col[0], end="\t")

# print("\n----------------------")

# while True:
#     row = c.fetchone()
#     if not row:
#         break
#     print(row)
#     print(row[1] + "---->" + row[2])

# c.close()
# conn.close()


# conn = sqlite3.connect('first.db')
# c = conn.cursor()
# c.execute('select * from user_tb where _id > ?', (2,))
# for col in c.description:
#     print(col[0], end='\t')
# print('\n-------------------')
# while True:
#     rows = c.fetchmany(3)
#     if not rows:
#         break
#     for r in rows:
#         print(r)
# c.close()
# conn.close()


# def reverse_ext(st):
#     return '[' + st[::-1] + ']'


# conn = sqlite3.connect('first.db')
# conn.create_function('enc', 1, reverse_ext)
# c = conn.cursor()
# c.execute('insert into user_tb values(null,?,enc(?),?)',
#           ('宋小宝', '123456', 'male'))
# conn.commit()
# c.close()
# conn.close()

# class Minlen:
#     def __init__(self):
#         self.min_len = None

#     def step(self, value):
#         if self.min_len is None:
#             self.min_len = value
#             return
#         if len(self.min_len) > len(value):
#             self.min_len = value

#     def finalize(self):
#         return self.min_len


# conn = sqlite3.connect('first.db')
# conn.create_aggregate('min_len', 1, Minlen)
# c = conn.cursor()
# c.execute('select min_len(pass) from user_tb')
# print(c.fetchone()[0])
# c.close()
# conn.close()

def my_collate(st1, st2):
    if st1[1:-1] == st2[1:-1]:
        return 0
    elif st1[1:-1] > st2[1:-1]:
        return 1
    else:
        return -1


conn = sqlite3.connect('first.db')
conn.create_collation('sub_cmp', my_collate)
c = conn.cursor()
c.execute('select * from user_tb order by pass collate sub_cmp')
for row in c:
    print(row)
c.close()
conn.close()
Mysql相关操作
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
import mysql.connector
print(mysql.connector.apilevel)
print(mysql.connector.paramstyle)
print(mysql.connector.threadsafety)


# conn = mysql.connector.connect(user='root', password='root',
#                                host='localhost', port='3306', database='python', use_unicode=True)
# c = conn.cursor()
# c.execute('''create table user_tb(
# 		user_id int primary key auto_increment,
# 		name varchar(255),
# 		pass varchar(255),
# 		gender varchar(255)
# 	)''')

# c.execute('''create table order_tb(
# 		order_id int primary key auto_increment,
# 		item_name  varchar(255),
# 		item_price double,
# 		item_number double,
# 		user_id int,
# 		foreign key(user_id) references user_tb(user_id)
# 	)''')

# c.close()
# conn.close()

# conn = mysql.connector.connect(user='root', password='root', host='localhost',
#                                port='3306', database='python', use_unicode=True)

# c = conn.cursor()
# c.execute('insert into user_tb values(null,%s,%s,%s)',
#           ('孙悟空', '123456', 'male'))
# c.execute('insert into order_tb values(null,%s,%s,%s,%s)',
#           ('鼠标', '34.2', '3', 1))
# conn.commit()
# c.close()
# conn.close()

# conn = mysql.connector.connect(user='root', password='root',
#                                host='localhost', port='3306', database='python', use_unicode=True)
# conn.autocommit = True
# c = conn.cursor()
# c.executemany('insert into user_tb values(null,%s,%s,%s)',
#               (('sun', '123456', 'male'),
#                ('bai', '123456', 'female'),
#                ('zhu', '123456', 'male'),
#                ('niu', '123456', 'male'),
#                ('tang', '123456', 'male')))

# c.close()
# conn.close()

# conn = mysql.connector.connect(user='root', password='root',
#                                host='localhost', port='3306', database='python', use_unicode=True)
# c = conn.cursor()
# c.execute('select * from user_tb where user_id > %s', (2,))
# for dec in c.description:
#     print(dec[0], end='\t')
# print('\n-------------------')
# for row in c:
#     print(row)
#     print(row[1] + '---->' + row[2])
# c.close()
# conn.close()


conn = mysql.connector.connect(user='root', password='root',
                               host='localhost', port='3306', database='python', use_unicode=True)
c=conn.cursor()

result_args = c.callproc('add_pro',(5,6,0))
print(result_args)
print(result_args[2])
c.close()
conn.close()

mysql 需要自行安装mysql数据库以及mysql-connector-python 依赖包