mysql 数据驱动_二.MySQL数据驱动

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('请输入用户名

')

age = input('请输入用户年龄

')

result = checkValid(username,age)

if result:

'''关闭数据库'''

MysqlTry().connectMysql().close()

print('该用户在数据库中，测试通过！')

else:

print('该用户不在数据库中，存在bug！')

if __name__ == '__main__':

checkinfo()