python操作mysql
一、python操作数据库
1、下载pymysql 库，
方法一：pip3 install pymysql 或pip install pymysql
方法二：在pycharm中setting下载pymysql
===============================
2、打开虚拟机上的数据库
===============================
3、pymysql连接
db=pymysql.Connection(host="192.168.157.128",user="root",password="123456",database="test",port=3306,charset='utf8')
（1）连接方式：pymysql.Connection 或者pymysql.connect
（2）包含内容 a.host 主机：填写IP地址 b.user 数据库用户名 c.password 或passwd 密码： d.databases 或db 库名 e.port 端口 ：默认3306 f.chardet ='utf8' 编码格式 （2）将连接内容设置成一个变量，然后创建一个游标对象
db.cursor （3）使用游标对象去执行sql语句 （4）在根据需要显示内容使用 fetchone，fetchall，fetchmany
案例1：查询
import pymysql db=pymysql.Connection(host="192.168.157.128",user="root",password="123456",database="test",port=3306,charset='utf8') yb=db.cursor()#创建游标 sql="select * from emp" yb.execute(sql) # one=yb.fetchone() #获取第一行数据 # print(one) # many=yb.fetchmany(size=3) # print(many)#获取部分数据 all=yb.fetchall() #获取所有数据 print(all)案例2：删除语句
import pymysql db=pymysql.connect(host="192.168.157.128",user="root",password="123456",database="test",port=3306,charset='utf8') #db1=pymysql.Connection(host="192.168.157.128",user="root",password="123456",database="test",port=3306,charset='utf8') yb=db.cursor()#创建游标 sql="delete from emp where name='张三'" yb.execute(sql)案例3： 插入数据
import pymysql
db=pymysql.connect(host="192.168.157.128",user="root",password="123456",database="test",port=3306,charset='utf8')
#db1=pymysql.Connection(host="192.168.157.128",user="root",password="123456",database="test",port=3306,charset='utf8')
yb=db.cursor()#创建游标
sql="insert into emp VALUES ('1879','张三',55,'1971/10/20',7300,'101');"
yb.execute(sql)
sql1="select * from emp"
yb.execute(sql1)
all=yb.fetchall() #获取所有数据
for i in all:print(i)
案例3： 修改数据
import pymysql db=pymysql.connect(host="192.168.157.128",user="root",password="123456",database="test",port=3306,charset='utf8') #db1=pymysql.Connection(host="192.168.157.128",user="root",password="123456",database="test",port=3306,charset='utf8') yb=db.cursor()#创建游标 sql="UPDATE emp SET name='zhan' where sid=1674 " yb.execute(sql) sql1="select * from emp" yb.execute(sql1) all=yb.fetchall() #获取所有数据 for i in all:print(i) ============================================
import pymysql
class Db_hz(object):def __init__(self,host,user,passwd,db,port):self.host=hostself.user=userself.passwd=passwdself.db=dbself.port=portdef lj(self):l=pymysql.Connection(host=self.host,user=self.user,passwd=self.passwd,db=self.db,port=self.port,charset="utf8")return ldef one(self,sql):d=self.lj()yb=d.cursor()yb.execute(sql)one1=yb.fetchone()print(one1)def many(self, sql):d = self.lj()yb = d.cursor()yb.execute(sql)many= yb.fetchmany(size=2)print(many)def all(self, sql):d = self.lj()yb = d.cursor()yb.execute(sql)all = yb.fetchall()print(all)
if __name__ == '__main__':dx=Db_hz("192.168.157.128","root","123456","test",3306)# dx.one("select * from emp")# dx.many("select * from emp")dx.all("select * from emp")
