1.下载pymysql
pip install pymysql
2 连接数据库
1 创建数据表
import pymysql
#1. 连接数据库,
conn = pymysql.connect(
host='localhost',
user='root',
password='redhat',
db='helloTest',
charset='utf8',
# autocommit=True, # 如果插入数据,, 是否自动提交? 和conn.commit()功能一致。
)
#****python, 必须有一个游标对象, 用来给数据库发送sql语句, 并执行的.
# 2. 创建游标对象,
cur = conn.cursor()
# 3. 对于数据库进行增删改查
# ***********************创建数据表**********************************
try:
create_sqli = "create table hello (id int, name varchar(30));"
cur.execute(create_sqli)
except Exception as e:
print("创建数据表失败:", e)
else:
print("创建数据表成功;")
# 4. 关闭游标
cur.close()
# 5. 关闭连接
conn.close()
2 插入数据
import pymysql
#1. 连接数据库,
conn = pymysql.connect(
host='localhost',
user='root',
password='redhat',
db='helloTest',
charset='utf8',
# autocommit=True, # 如果插入数据,, 是否自动提交? 和conn.commit()功能一致。
)
#****python, 必须有一个游标对象, 用来给数据库发送sql语句, 并执行的.
# 2. 创建游标对象,
cur = conn.cursor()
#*********************插入数据****************************
try:
insert_sqli = "insert into hello values(2, 'fensi');"
cur.execute(insert_sqli)
except Exception as e:
print("插入数据失败:", e)
else:
# 如果是插入数据, 一定要提交数据, 不然数据库中找不到要插入的数据;
conn.commit()
print("插入数据成功;")
# 4. 关闭游标
cur.close()
# 5. 关闭连接
conn.close()
插入多条数据
第一种方法
只改try里面的内容,外的内容不变。
# *********************插入多条数据****************************
try:
info = [(i, "westos%s" %(i)) for i in range(100)]
# [(0, 'westos0'), (1, 'westos1'), (2, 'westos2'), (3, 'westos3'), (4, 'westos4'), (5, 'westos5'),........]
# *********************第一种方式********************
# # %s必须手动添加一个字符串, 否则就是一个变量名, 会报错.
insert_sqli = "insert into hello values(%d, '%s');"
for item in info:
print('insert语句:', insert_sqli %item)
cur.execute(insert_sqli %item)
第二种方法
try:
info = [(i, "westos%s" %(i)) for i in range(100)]
insert_sqli = "insert into hello values(%s, %s);"
cur.executemany(insert_sqli, info )
except Exception as e:
print("插入多条数据失败:", e)
else:
# 如果是插入数据, 一定要提交数据, 不然数据库中找不到要插入的数据;
conn.commit()
print("插入多条数据成功;")
4数据库查询
直接打印不能显示数据库里的内容
import pymysql
# 1. 连接数据库
conn = pymysql.connect(
host='localhost',
user='root',
password='westos',
db='helloTest',
charset='utf8',
# autocommit=True, # 如果插入数据,, 是否自动提交? 和conn.commit()功能一致。
)
# ****python, 必须有一个游标对象, 用来给数据库发送sql语句, 并执行的.
# 2. 创建游标对象,
cur = conn.cursor()
# 以上都是公共部分,下面不再重复写
#用打印查询只能返回记录数
sqli = "select * from hello;"
result = cur.execute(sqli) # 默认不返回查询结果集, 返回数据记录数。
print(result)
显示数据库里详细信息:
sqli = "select * from hello;"
cur.execute(sqli)
1). 获取一个查询结果集;
print(cur.fetchone())
print(cur.fetchone())
print(cur.fetchone())
2). 获取制定个数个查询结果集;
print(cur.fetchmany(4))
3). 获取所有的查询结果
info = cur.fetchall()
print(info)
print(len(info)) #info的长度
输出:
((2, 'fensi'), (0, 'westos0'), (1, 'westos1'), (2, 'westos2'), (3, 'westos3'), (4, 'westos4'), (5, 'westos5'), (6, 'westos6'), (7, 'westos7'), (8, 'westos8'), (9, 'westos9'), (10, 'westos10'), (11, 'westos11'), (12, 'westos12'), (13, 'westos13'), (14, 'westos14'), (15, 'westos15'), (16, 'westos16'), (17, 'westos17'), (18, 'westos18'), (19, 'westos19'), (20, 'westos20'), (21, 'westos21'), (22, 'westos22'), (23, 'westos23'), (24, 'westos24'), (25, 'westos25'), (26, 'westos26'), (27, 'westos27'), (28, 'westos28'), (29, 'westos29'), (30, 'westos30'), (31, 'westos31'), (32, 'westos32'), (33, 'westos33'), (34, 'westos34'), (35, 'westos35'), (36, 'westos36'), (37, 'westos37'), (38, 'westos38'), (39, 'westos39'), (40, 'westos40'), (41, 'westos41'), (42, 'westos42'), (43, 'westos43'), (44, 'westos44'), (45, 'westos45'), (46, 'westos46'), (47, 'westos47'), (48, 'westos48'), (49, 'westos49'), (50, 'westos50'), (51, 'westos51'), (52, 'westos52'), (53, 'westos53'), (54, 'westos54'), (55, 'westos55'), (56, 'westos56'), (57, 'westos57'), (58, 'westos58'), (59, 'westos59'), (60, 'westos60'), (61, 'westos61'), (62, 'westos62'), (63, 'westos63'), (64, 'westos64'), (65, 'westos65'), (66, 'westos66'), (67, 'westos67'), (68, 'westos68'), (69, 'westos69'), (70, 'westos70'), (71, 'westos71'), (72, 'westos72'), (73, 'westos73'), (74, 'westos74'), (75, 'westos75'), (76, 'westos76'), (77, 'westos77'), (78, 'westos78'), (79, 'westos79'), (80, 'westos80'), (81, 'westos81'), (82, 'westos82'), (83, 'westos83'), (84, 'westos84'), (85, 'westos85'), (86, 'westos86'), (87, 'westos87'), (88, 'westos88'), (89, 'westos89'), (90, 'westos90'), (91, 'westos91'), (92, 'westos92'), (93, 'westos93'), (94, 'westos94'), (95, 'westos95'), (96, 'westos96'), (97, 'westos97'), (98, 'westos98'), (99, 'westos99'))
101
4).返回执行sql语句影响的行数
print(cur.rowcount) # 4). 返回执行sql语句影响的行数与len和print(cur.execute(sqli))答案相同
5). 移动游标指针
1)移动到最开始
print(cur.fetchmany(3))
print("正在移动指针到最开始......")
cur.scroll(0, 'absolute')
print(cur.fetchmany(3))
2)移动到到数第二个
print("正在移动指针到倒数第2个......")
print(cur.fetchall()) # 移动到最后
cur.scroll(-2, mode='relative')
输出:
正在移动指针到倒数第2个......
((2, 'fensi'), (0, 'westos0'), (1, 'westos1'), (2, 'westos2'), (3, 'westos3'), (4, 'westos4'), (5, 'westos5'), (6, 'westos6'), (7, 'westos7'), (8, 'westos8'), (9, 'westos9'), (10, 'westos10'), (11, 'westos11'), (12, 'westos12'), (13, 'westos13'), (14, 'westos14'), (15, 'westos15'), (16, 'westos16'), (17, 'westos17'), (18, 'westos18'), (19, 'westos19'), (20, 'westos20'), (21, 'westos21'), (22, 'westos22'), (23, 'westos23'), (24, 'westos24'), (25, 'westos25'), (26, 'westos26'), (27, 'westos27'), (28, 'westos28'), (29, 'westos29'), (30, 'westos30'), (31, 'westos31'), (32, 'westos32'), (33, 'westos33'), (34, 'westos34'), (35, 'westos35'), (36, 'westos36'), (37, 'westos37'), (38, 'westos38'), (39, 'westos39'), (40, 'westos40'), (41, 'westos41'), (42, 'westos42'), (43, 'westos43'), (44, 'westos44'), (45, 'westos45'), (46, 'westos46'), (47, 'westos47'), (48, 'westos48'), (49, 'westos49'), (50, 'westos50'), (51, 'westos51'), (52, 'westos52'), (53, 'westos53'), (54, 'westos54'), (55, 'westos55'), (56, 'westos56'), (57, 'westos57'), (58, 'westos58'), (59, 'westos59'), (60, 'westos60'), (61, 'westos61'), (62, 'westos62'), (63, 'westos63'), (64, 'westos64'), (65, 'westos65'), (66, 'westos66'), (67, 'westos67'), (68, 'westos68'), (69, 'westos69'), (70, 'westos70'), (71, 'westos71'), (72, 'westos72'), (73, 'westos73'), (74, 'westos74'), (75, 'westos75'), (76, 'westos76'), (77, 'westos77'), (78, 'westos78'), (79, 'westos79'), (80, 'westos80'), (81, 'westos81'), (82, 'westos82'), (83, 'westos83'), (84, 'westos84'), (85, 'westos85'), (86, 'westos86'), (87, 'westos87'), (88, 'westos88'), (89, 'westos89'), (90, 'westos90'), (91, 'westos91'), (92, 'westos92'), (93, 'westos93'), (94, 'westos94'), (95, 'westos95'), (96, 'westos96'), (97, 'westos97'), (98, 'westos98'), (99, 'westos99'))
((98, 'westos98'), (99, 'westos99'))