python连接MySQL

python连接MySQL

pymysql

通过pymysql进行数据库操作

创建表

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
import pymysql

# 1.创建连接
conn = pymysql.connect(host="127.0.0.1",
                       port=3306,
                       user='root',
                       password='123456',
                       charset='utf8',
                       db='pyconn')

# 2.创建游标（cursor）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 3.执行sql
sql = """CREATE TABLE admin(
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(16) NOT NULL,
	PASSWORD VARCHAR(64) NOT NULL,
	mobile CHAR(11) NOT NULL
) DEFAULT CHARSET=utf8;"""

cursor.execute(sql)

# 4.断开连接
conn.close()

插入数据

要使用connit提交到数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 1.创建连接
conn = pymysql.connect(host="127.0.0.1",
                       port=3306,
                       user='root',
                       password='123456',
                       charset='utf8',
                       db='pyconn')
# 2.创建游标（cursor）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 3.用游标execute执行sql语句
cursor.execute("insert into admin(username, password, mobile) values ('zs','123456','12312345678')")
conn.commit()

# 4.断开连接
conn.close()

注意：

1
2
3
# 不要用拼接字符串来注入变量，而是使用pymysql的占位符
sql = "insert into admin(username, password, mobile) values (%s,%s,%s)"
cursor.execute(sql, ['ls', '123456', '45612345678'])
1
2
3
# 可以给占位符起名
sql = "insert into admin(username, password, mobile) values (%(n1)s,%(n2)s,%(n3)s)"
cursor.execute(sql, {'n1': 'ww', 'n2': '123456', 'n3': '12345678123'})

查询数据

fetchone()：查询一条

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
import pymysql

# 1.创建连接
conn = pymysql.connect(host="127.0.0.1",
                       port=3306,
                       user='root',
                       password='123456',
                       charset='utf8',
                       db='pyconn')
# 2.创建游标（cursor）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 3.执行sql
sql = 'select * from admin'
cursor.execute(sql)

# 3.1fetchone
data = cursor.fetchone()

# 3.2fetchall
data_list = cursor.fetchall()
for row in data_list:
    print(row)

# 4.断开连接
conn.close()

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import pymysql

# 1.创建连接
conn = pymysql.connect(host="127.0.0.1",
                       port=3306,
                       user='root',
                       password='123456',
                       charset='utf8',
                       db='pyconn')
# 2.创建游标（cursor）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 3.执行sql
sql = 'update admin set password = %s where id = %s'
cursor.execute(sql, ['987654', '1'])
conn.commit()

# 4.断开连接
conn.close()

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import pymysql

# 1.创建连接
conn = pymysql.connect(host="127.0.0.1",
                       port=3306,
                       user='root',
                       password='123456',
                       charset='utf8',
                       db='pyconn')
# 2.创建游标（cursor）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 3.执行sql
sql = 'delete from admin where id=%s'
cursor.execute(sql, [1, ])
conn.commit()

# 4.断开连接
conn.close()

事务

1
2
3
4
5
6
try: 
    sql = 'delete from admin where id=%s'
    cursor.execute(sql, [1, ])
    conn.commit()
except:
    conn.rollback()	# 回滚事务
学习笔记 > python
#其他
python连接MySQL
http://xwww12.github.io/2022/12/01/python/python连接MySQL/
作者
xw
发布于
2022年12月1日
许可协议