python+pymysql(16)

python操作mysql

一、python操作数据库

1、下载pymysql 库，

方法一：pip3 install pymysql 或pip install pymysql

方法二：在pycharm中setting下载pymysql

===============================
2、打开虚拟机上的数据库

===============================
3、pymysql连接

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

案例4：修改数据
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=host
        self.user=user
        self.passwd=passwd
        self.db=db
        self.port=port
    def lj(self):
        l=pymysql.Connection(host=self.host,user=self.user,passwd=self.passwd,db=self.db,port=self.port,charset="utf8")
        return l
    def 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")