python - 用pymysql连接数据库和python

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'))