1.数据库查询
#!/user/bin/env python
# coding:utf-8
# Author:shenqiang
import pymysql
def connectMysql():
try:
'''链接数据库'''
connect = pymysql.connect(
host='127.0.0.1',
user='root',
password='shen6409175',
db='students'
)
except Exception as e:
return e.args
else:
'''创建游标'''
cur = connect.cursor()
'''SQL语句分离'''
# sql = 'select * from student where id = %s'
# params = (2,)
# '''查重'''
# cur.execute(sql,params)
# '''单条数据的查询'''
# data = cur.fetchone()
# return datas
sql = 'select * from student'
'''查重'''
cur.execute(sql)
'''多条数据查询'''
datas = cur.fetchall()
'''方法一,遍历'''
# for data in datas:
# print(data)
'''方法二,列表推倒式'''
db = [data for data in datas]
return db
finally:
# 关闭游标和链接
cur.close()
connect.close()
print(connectMysql())
2.数据库插入数据
#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
import pymysql
def connectMysql():
try:
'''链接数据库'''
connect = pymysql.connect(
host='127.0.0.1',
user='root',
password='shen6409175',
db='students'
)
except Exception as e:
return e.args
else:
'''创建游标'''
cur = connect.cursor()
'''导入数据'''
# 单条语句的插入
# sql = 'insert into student values(%s,%s,%s,%s);'
# params = (6,'沈~','24','南京')
'''批量插入数据'''
sql = 'insert into student values(%s,%s,%s,%s);'
params = [
(7, '沈~', '24', '南京'),
(8, '沈~', '24', '南京')
]
cur.executemany(sql,params)
'''insert后必须要commit()'''
connect.commit()
finally:
# 关闭游标和链接
cur.close()
connect.close()
connectMysql()
3.数据库删除数据
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author : shenqiang
import pymysql
def connectMysqlDelect():
try:
'''连接数据库'''
connectMysqlDelect = pymysql.Connect(
host = '127.0.0.1',
user = 'root',
password = 'shen6409175',
db = 'students',
)
except Exception as e:
print(e.args)
else:
'''创建游标'''
cur = connectMysqlDelect.cursor()
'''执行sql'''
mysql = 'delete from student order by id desc limit 1'
cur.execute(mysql)
'''提交事务'''
connectMysqlDelect.commit()
print('success')
finally:
'''关闭游标和数据库'''
cur.close()
connectMysqlDelect.close()
connectMysqlDelect()
4.一个完整的Mysql数据驱动方式
#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
import pymysql
class MysqlTry:
'''链接数据库'''
def connectMysql(self):
'''尝试链接数据库'''
try:
connect =pymysql.connect(
host = '127.0.0.1',
user='root',
password='shen6409175',
db='students'
)
except Exception as e:
print(e.args)
return connect
def selectMysql(self,sql,params):
'''创建游标'''
cur = self.connectMysql().cursor()
'''查重'''
cur.execute(sql,params)
'''查询'''
result = cur.fetchall()
'''删除游标'''
cur.close()
return result
def checkValid(username,age):
opera = MysqlTry()
sql = "select * from student where name = %s and age = %s"
params=(username,age)
return opera.selectMysql(sql=sql,params=params)
def checkinfo():
username = input('请输入用户名 \n')
age = input('请输入用户年龄 \n')
result = checkValid(username,age)
if result:
'''关闭数据库'''
MysqlTry().connectMysql().close()
print('该用户在数据库中,测试通过!')
else:
print('该用户不在数据库中,存在bug!')
if __name__ == '__main__':
checkinfo()