1，连接数据库

import pymysql

#连接数据库
#参数1：MySQL服务所在的主机ip
#参数2：用户名
#参数3：密码
#参数4： 要连接的数据库名
#参数5： 服务的端口号
#db = pymysql.connect("localhost","root","123.xxx","yichen",3308)
db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)
#创建一个cursor对象
cursor = db.cursor()

sql = "select version()"

#执行sql语句
cursor.execute(sql)




#获取返回的信息
data =cursor.fetchone()

print(data)

#断开
cursor.close()
db.cursor()

2,创建数据库表

import pymysql

db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)

cursor = db.cursor()
#检查表是否存在，如果存在则删除
cursor.execute("drop table if exists bandcard")

#建表
sql = "create table bandcard(id int auto_increment primary key, money int not null)"

cursor.execute(sql)



#断开
cursor.close()
db.cursor()

3,数据库插入数据

import pymysql

db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)

cursor = db.cursor()


#插入数据
sql = "insert into bandcard values(0,100)"
try:

    cursor.execute(sql)
    db.commit()
except:
    #如果提交失败，回滚到上一次数据
    db.rollback()



#断开
cursor.close()
db.cursor()

4,数据库更新操作

import pymysql

db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)

cursor = db.cursor()


#插入数据
sql = "update bandcard set money=1000 where id=1"
try:

    cursor.execute(sql)
    db.commit()
except:
    #如果提交失败，回滚到上一次数据
    db.rollback()



#断开
cursor.close()
db.cursor()

5，数据库删除操作

import pymysql

db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)

cursor = db.cursor()


#插入数据
sql = "delete from bandcard where money = 1000"
try:

    cursor.execute(sql)
    db.commit()
except:
    #如果提交失败，回滚到上一次数据
    db.rollback()



#断开
cursor.close()
db.cursor()

6,数据库查询操作

import pymysql
'''
fetchone()
功能：获取下一个查询结果集，结果集是一个对象

fetchall()
功能：接收全部的返回的行

rowcount: 是一个只读属性，返回execute()方法影响的行数

'''


db = pymysql.connect("192.168.1.132","root","123.xxx","yichen",3308)

cursor = db.cursor()

sql = "select * from bandcard where money>400"
try:
    cursor.execute(sql)
    reslist =cursor.fetchall()

    for row in reslist:
        print("%d---%d"%(row[0],row[1]))


except:
   db.rollback()


#断开
cursor.close()
db.cursor()

7,封装yichenSql

import pymysql

class YichenSql():
    def __init__(self,host,user,passwd,dbName,port):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.dbName =dbName
        self.port =port

    def connet(self):
        self.db= pymysql.connect(self.host,self.user,self.passwd,self.dbName,self.port)

        self.cursor = self.db.cursor()

    def close(self):
        self.cursor.close()
        self.db.close()

    def get_one(self,sql):
        res = None
        try:
            self.connet()
            self.cursor.execute(sql)
            res = self.cursor.fetchone()
            self.close()

        except:
            print("查询失败")
        return res

    def get_all(self,sql):
        res = None
        try:
            self.connet()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()

        except:
            print("查询失败")
        return res

    def insert(self,sql):
        return self.__edit(sql)
    def update(self,sql):
        return self.__edit(sql)
    def delete(self):
        return self.__edit(sql)


    def __edit(self,sql):
        count = 0

        try:
            self.connet()

            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except:
            print("事物提交失败")
            self.db.rollback()

8，测试封装的库

from yichenSql import YichenSql


#创建对象
s = YichenSql("192.168.1.132","root","123.xxx","yichen",3308)

res = s.get_all("select * from bandcard where money>400")

for row in res:
    print("%d---%d" % (row[0], row[1]))