链接mysql
- 安装pymysql包；
- host不用写端口号；
- 已脱敏；
```python
import pymysql
connect = pymysql.connect(
host=’aaaaaaa.mysql.rds.aliyuncs.com’,
user=’AAAA’,
password=’AAAAAA’,
db=’test’,
charset=’utf8mb4’
)
cur = connect.cursor()
print(cur)
- 返回数据如下，是一个游标实例对象，表明连接成功。
```python
<pymysql.cursors.Cursor object at 0x00000164DB2B86A0>
创建表
- 代码如下
- 最好每次链接之后都关闭游标，关闭数据库链接
```python
import pymysql
connect = pymysql.connect(
host=’aaaaaaa.mysql.rds.aliyuncs.com’,
user=’AAAA’,
password=’AAAAAA’,
db=’test’,
charset=’utf8mb4’
)
cur = connect.cursor()
print(cur)
#创建表
sql1 =”””
CREATE TABLE test01 (
id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘ID’,
key1 VARCHAR(40) DEFAULT ‘’ COMMENT ‘key1’,
remark varchar(255) DEFAULT ‘’ COMMENT ‘备注’,
state tinyint(4) DEFAULT ‘0’ COMMENT ‘状态 0-正常99-删除 ‘,
version int(11) DEFAULT ‘0’ COMMENT ‘版本号’,
created_at datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
created_operator_id varchar(40) DEFAULT ‘’ COMMENT ‘创建人’,
created_operator_name varchar(40) DEFAULT ‘’ COMMENT ‘创建人名称’,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改日期’,
updated_operator_id varchar(40) DEFAULT ‘’ COMMENT ‘修改人’,
updated_operator_name varchar(40) DEFAULT ‘’ COMMENT ‘修改名称’,
PRIMARY KEY (id),
key ind_key1(key1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT ‘测试1’;
“””
cur.execute(sql1)
cur.close
connect.close
- 结果如下，实际确实创建成功了
- 但是由于程序是要可以重复执行的，所以要设置异常处理，也可以把sql1变量放到try里；
```python
try:
cur.execute(sql1)
except Exception as e:
print("创建数据表失败！",e)
else:
print("创建数据表成功！")
报错如下：
创建数据表失败！ (1050, "Table 'test01' already exists")增加数据
注意一定要commit,否则实际是不会插入数据的，而且会占用一个自增ID，下一次再插入输入，ID会为3；
数据 增、删、改 都需要加commit语句；
表结构的增、删、改 都不需要加commit语句,这些是默认commit的；
sql2 =""" INSERT INTO test01(key1) VALUES ("测试"); """ try: cur.execute(sql2) except Exception as e: print("插入数据表失败！",e) else: connect.commit() print("插入数据表成功！")查询
查询有点特殊，需要用到游标来进行展示；
cur.execute(sql3) 返回是查询的多少条结果
fetchone()函数它的返回值是单个的元组，也就是一行记录,如果没有结果,那就会返回null
fetchall()函数,它的返回值是多个元组,即返回多个行记录,如果没有结果,返回的是()
注意：游标是会顺序访问的，是个迭代器。
# 查询数据 sql3 =""" SELECT * from test01 WHERE id=1; """ try: cur.execute(sql3) except Exception as e: print("查询数据表失败！",e) else: print("查询数据表成功！") one = cur.fetchone() print(one)返回结果如下：
查询数据表成功！ (1, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 29, 48), '', '', datetime.datetime(2022, 7, 1, 10, 29, 48), '', '')查询所有数据
# 查询数据 sql3 =""" SELECT * from test01 WHERE 1=1; """ try: count=cur.execute(sql3) except Exception as e: print("查询数据表失败！",e) else: print("查询数据表成功！") print("查询结果是几条数据：",count) print("其中一条结果是：") one = cur.fetchone() print(one) print("其中3条结果是：") many = cur.fetchmany(3) print(many) print("展示所有结果：") for each in range(count): print(cur.fetchone())结果如下
查询数据表成功！ 查询结果是几条数据： 8 其中一条结果是： (1, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 29, 48), '', '', datetime.datetime(2022, 7, 1, 10, 29, 48), '', '') 其中3条结果是： ((3, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 33, 13), '', '', datetime.datetime(2022, 7, 1, 10, 33, 13), '', ''), (4, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 35, 32), '', '', datetime.datetime(2022, 7, 1, 10, 35, 32), '', ''), (5, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 38, 22), '', '', datetime.datetime(2022, 7, 1, 10, 38, 22), '', '')) 展示所有结果： (6, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 52, 26), '', '', datetime.datetime(2022, 7, 1, 10, 52, 26), '', '') (7, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 56, 9), '', '', datetime.datetime(2022, 7, 1, 10, 56, 9), '', '') (8, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 58, 27), '', '', datetime.datetime(2022, 7, 1, 10, 58, 27), '', '') (9, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 59, 4), '', '', datetime.datetime(2022, 7, 1, 10, 59, 4), '', '') None None None None可以发现结果不对，这个是游标的顺序访问造成的，除非前面不查。
```python
查询数据
sql3 =”””
SELECT * from test01 WHERE 1=1;
“””
try:
count=cur.execute(sql3)
except Exception as e:
print(“查询数据表失败！”,e)
else:
print(“查询数据表成功！”)
print(“查询结果是几条数据：”,count)
print("展示所有结果：")
print(cur.fetchall())
- 结果如下：
```python
查询数据表成功！
查询结果是几条数据： 9
展示所有结果：
((1, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 29, 48), '', '', datetime.datetime(2022, 7, 1, 10, 29, 48), '', ''), (3, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 33, 13), '', '', datetime.datetime(2022, 7, 1, 10, 33, 13), '', ''), (4, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 35, 32), '', '', datetime.datetime(2022, 7, 1, 10, 35, 32), '', ''), (5, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 38, 22), '', '', datetime.datetime(2022, 7, 1, 10, 38, 22), '', ''), (6, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 52, 26), '', '', datetime.datetime(2022, 7, 1, 10, 52, 26), '', ''), (7, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 56, 9), '', '', datetime.datetime(2022, 7, 1, 10, 56, 9), '', ''), (8, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 58, 27), '', '', datetime.datetime(2022, 7, 1, 10, 58, 27), '', ''), (9, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 10, 59, 4), '', '', datetime.datetime(2022, 7, 1, 10, 59, 4), '', ''), (10, '测试', '', 0, 0, datetime.datetime(2022, 7, 1, 11, 20, 31), '', '', datetime.datetime(2022, 7, 1, 11, 20, 31), '', ''))
删除表
- 把sql1 改成drop语句就行
# 删除表 sql4 =""" drop table if EXISTS test01 ; """ try: count=cur.execute(sql4) except Exception as e: print("删除表失败！",e) else: print("删除表成功！")完整语句
```python
import pymysql
connect = pymysql.connect(
host=’aaaaaaa.mysql.rds.aliyuncs.com’,
user=’AAAA’,
password=’AAAAAA’,
db=’test’,
charset=’utf8mb4’
)
cur = connect.cursor()
print(cur)
创建表
sql1 =”””
CREATE TABLE test01 (
id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘ID’,
key1 VARCHAR(40) DEFAULT ‘’ COMMENT ‘key1’,
remark varchar(255) DEFAULT ‘’ COMMENT ‘备注’,
state tinyint(4) DEFAULT ‘0’ COMMENT ‘状态 0-正常99-删除 ‘,
version int(11) DEFAULT ‘0’ COMMENT ‘版本号’,
created_at datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
created_operator_id varchar(40) DEFAULT ‘’ COMMENT ‘创建人’,
created_operator_name varchar(40) DEFAULT ‘’ COMMENT ‘创建人名称’,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改日期’,
updated_operator_id varchar(40) DEFAULT ‘’ COMMENT ‘修改人’,
updated_operator_name varchar(40) DEFAULT ‘’ COMMENT ‘修改名称’,
PRIMARY KEY (id),
key ind_key1(key1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT ‘测试1’;
“””
try:
cur.execute(sql1)
except Exception as e:
print(“创建数据表失败！”,e)
else:
print(“创建数据表成功！”)
插入数据
sql2 =”””
INSERT INTO test01(key1)
VALUES (“测试”);
“””
try:
cur.execute(sql2)
except Exception as e:
print(“插入数据表失败！”,e)
else:
connect.commit()
print(“插入数据表成功！”)
查询数据
sql3 =”””
SELECT * from test01 WHERE 1=1;
“””
try:
count=cur.execute(sql3)
except Exception as e:
print(“查询数据表失败！”,e)
else:
print(“查询数据表成功！”)
print(“查询结果是几条数据：”,count)
print("展示所有结果：")
print(cur.fetchall())
删除表
sql4 =”””
drop table if EXISTS test01 ;
“””
try:
count=cur.execute(sql4)
except Exception as e:
print(“删除表失败！”,e)
else:
print(“删除表成功！”)
cur.close
connect.close
