Python之MySQL数据库操作
Python之连接数据库
import pymysql
# 获取连接对象conn,建立数据库的连接
def get_conn():
conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test1') # db:表示数据库名称
return conn
1
2
3
4
5
6
Python之插入操作
import pymysql
def get_conn():
conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test1') # db:表示数据库名称
return conn
def insert(sql):
conn = get_conn()
cur = conn.cursor()
result = cur.execute(sql)
print(result)
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
sql = 'INSERT INTO test_student_table VALUES(1,\'zhang\',12);''
insert(sql)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
在写sql语句的时候,需要避免直接写sql语句,而是采用占位符的方式来,防止sql的注入。
sql占位符形式实现
import pymysql
def get_conn():
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test1')
return conn
def insert(sql, args):
conn = get_conn()
cur = conn.cursor()
result = cur.execute(sql, args)
print(result)
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
sql = 'INSERT INTO test_student_table VALUES(%s,%s,%s);'
insert(sql, (2, 'wang', 13))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
下面代码均以占位符的形式实现sql语句。
插入多条语句实现
import pymysql
def get_conn():
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test1')
return conn
def insert_many(sql, args):
conn = get_conn()
cur = conn.cursor()
result = cur.executemany(query=sql, args=args)
print(result)
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
sql = 'insert into test_student_table VALUES (%s,%s,%s)'
args = [(3, 'li', 11), (4, 'sun', 12), (5, 'zhao', 13)]
insert_many(sql=sql, args=args)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Python之更新操作
import pymysql
def get_conn():
conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test1') # db:表示数据库名称
return conn
def update(sql,args):
conn = get_conn()
cur = conn.cursor()
result = cur.execute(sql,args)
print(result)
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
sql = 'UPDATE test_student_table SET NAME=%s WHERE id = %s;'
args = ('zhangsan', 1)
update(sql, args)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Python之删除操作
import pymysql
def get_conn():
conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test1') # db:表示数据库名称
return conn
def delete(sql,args):
conn = get_conn()
cur = conn.cursor()
result = cur.execute(sql,args)
print(result)
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
sql = 'DELETE FROM test_student_table WHERE id = %s;'
args = (1,) # 单个元素的tuple写法
delete(sql,args)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Python之查询操作
import pymysql
def get_conn():
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='test1')
return conn
def query(sql,args):
conn = get_conn()
cur = conn.cursor()
cur.execute(sql,args)
results = cur.fetchall()
print(type(results)) # 返回<class 'tuple'> tuple元组类型
for row in results:
print(row)
id = row[0]
name = row[1]
age = row[2]
print('id: ' + str(id) + ' name: ' + name + ' age: ' + str(age))
pass
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
sql = 'SELECT * FROM test_student_table;'
query(sql,None)
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
输出:
<class 'tuple'>
(2, 'wang', 13)
id: 2 name: wang age: 13
(3, 'li', 11)
id: 3 name: li age: 11
(4, 'sun', 12)
id: 4 name: sun age: 12
(5, 'zhao', 13)
id: 5 name: zhao age: 13
---------------------
【转】
作者:张行之
来源:CSDN
原文:https://blog.csdn.net/qq_33689414/article/details/78306978
版权声明:本文为博主原创文章,转载请附上博文链接!
|
|