Python学习日记(三十七) Mysql数据库篇五

pymsql的使用

初识pymysql模块

先在数据库中创建一个用户信息表,里面包含用户的ID、用户名、密码

create table userinfo(

    uid int not null auto_increment primary key,

    username varchar(32),

    pwd varchar(32)

)engine = innodb default charset=utf8;

增加一个用户的信息:

insert into userinfo(username,pwd) values('jxson','a123');

用pymysql模块从数据库拿到用户信息模拟登陆效果:

import pymysql

user = input('username:')                                                               #输入用户的名字

pwd = input('password:')                                                                #输入用户的密码

conn = pymysql.connect(host ='localhost',user = 'root',password = '',database = 'db1')  #连接数据库

cursor = conn.cursor()                                                                  #cursor是一个游标 去帮我们获取数据

sql_search = "select * from userinfo where username='%s' and pwd='%s'" %(user,pwd,)     #SQL语句

cursor.execute(sql_search)                                                              #执行SQL语句

get_one = cursor.fetchone()                                                             #拿一组数据

cursor.close()                                                                          #关闭游标

conn.close()                                                                            #关闭连接

if get_one:                                                                             #判断是否有拿到数据

    print("登陆成功!")

    print(get_one)

else:

    print("登陆失败!")

执行结果:

username:jxson

password:a123

登陆成功!

(1, 'jxson', 'a123')

防止SQL注入的改进方法

sql_search = "select * from userinfo where username=%s and pwd=%s"

cursor.execute(sql_search,user,pwd)

2.列表的表示方式

sql_search = "select * from userinfo where username=%s and pwd=%s"

cursor.execute(sql_search,[user,pwd])

3.字典的表示方式

sql_search = "select * from userinfo where username=%(u)s and pwd=%(p)s"

cursor.execute(sql_search,{'u' : user,'p' : pwd})

增、删、改、查

增、删、改:

增、删、改都需要commit(),以增为举例

增加单个

import pymysql

conn = pymysql.connect(host ='localhost',user = 'root',password = '',database = 'db1')

cursor = conn.cursor()

sql_insert = "insert into userinfo(username,pwd) values('abc','666')"

cursor.execute(sql_insert)

conn.commit()

cursor.close()

conn.close()

增加多个:

import pymysql

conn = pymysql.connect(host ='localhost',user = 'root',password = '',database = 'db1')

cursor = conn.cursor()

sql_insert = "insert into userinfo(username,pwd) values(%s,%s)"

cursor.executemany(sql_insert,[('hylisang','a123'),('HSI15','47a8'),('asw2','')])

conn.commit()

cursor.close()

conn.close()

output:

查:

1.fetchone()

import pymysql

conn = pymysql.connect(host ='localhost',user = 'root',password = '',database = 'db1')

cursor = conn.cursor()

sql = "select * from userinfo"

cursor.execute(sql)

result1 = cursor.fetchone()

print(result1)

result2 = cursor.fetchone()

print(result2)

cursor.close()

conn.close()

执行结果:

(1, 'jxson', 'a123')

(6, 'abc', '')

2.fetchmany(n),一次取n条数据

import pymysql

conn = pymysql.connect(host ='localhost',user = 'root',password = '',database = 'db1')

cursor = conn.cursor()

sql = "select * from userinfo"

cursor.execute(sql)

result1 = cursor.fetchmany(3)

print(result1)

cursor.close()

conn.close()

执行结果:

((1, 'jxson', 'a123'), (6, 'abc', ''), (7, 'hylisang', 'a123'))

3.fetchall(),一次取全部SQL语句查到的数据

import pymysql

conn = pymysql.connect(host ='localhost',user = 'root',password = '',database = 'db1')

cursor = conn.cursor()

sql = "select * from userinfo"

cursor.execute(sql)

result1 = cursor.fetchall()

print(result1)

cursor.close()

conn.close()

执行结果:

((1, 'jxson', 'a123'), (6, 'abc', ''), (7, 'hylisang', 'a123'), (8, 'HSI15', '47a8'), (9, 'asw2', ''))

把结果转化为字典的格式:

import pymysql

conn = pymysql.connect(host ='localhost',user = 'root',password = '',database = 'db1')

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = "select * from userinfo"

cursor.execute(sql)

result1 = cursor.fetchall()

print(result1)

cursor.close()

conn.close()

执行结果:

[{'uid': 1, 'username': 'jxson', 'pwd': 'a123'}, {'uid': 6, 'username': 'abc', 'pwd': ''}, {'uid': 7, 'username': 'hylisang', 'pwd': 'a123'},
 {'uid': 8, 'username': 'HSI15', 'pwd': '47a8'}, {'uid': 9, 'username': 'asw2', 'pwd': ''}]

4.控制游标来获取数据

cursor.scroll(1,mode='relative')    　　　　　　　　　　　　　　　　　　　　　　　　　　　　#根据当前位置相对位移

cursor.scroll(2,mode='absolute')    　　　　　　　　　　　　　　　　　　　　　　　　　　　　#绝对位置移动

查看新插入数据的自增ID

如果添加多笔数据再去查询,那查到的ID是最后一条

import pymysql

conn = pymysql.connect(host ='localhost',user = 'root',password = '',database = 'db1')

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql_insert = "insert into userinfo(username,pwd) values('abc','666')"

cursor.execute(sql_insert)

print(cursor.lastrowid)

conn.commit()

cursor.close()

conn.close()

巴特西

Python学习日记(三十七) Mysql数据库篇五

最新文章

热门文章

巴特西

Python学习日记(三十七) Mysql数据库篇 五

最新文章

热门文章

Python学习日记(三十七) Mysql数据库篇五