试图5天学会python——pymysql

一、python 连接 mysql

import pymysql

class DataBase():
    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset

    # 建立数据库连接
    def connect(self):
        try:
            self.con = pymysql.connect(host=self.host, user=self.user, 
           								password=self.password,port=self.port, 
           								database=self.database, charset=self.charset, 
            							autocommit=True)
            print('数据库连接建立成功！')
        except Exception:
            print('数据库连接建立错误！')
        self.cur = self.con.cursor()  

    # 关闭数据游标和连接
    def close(self):
        self.cur.close()
        self.con.close()

    # 创建数据表
    def create(self, sql):
        self.connect()        
        self.sql = sql

        try:
            self.cur.execute(self.sql)
            print('创建数据表成功！')
        except Exception:
            print('创建表失败！')

        self.close()

    # 查询全部
    def select_all(self, sql, size=0):
        self.connect()

        self.sql = sql
        self.cur.execute(self.sql)  
        self.count = self.cur.rowcount  

        if size == 0:
            self.res = self.cur.fetchall()  
        elif size != 0:
            self.res = self.cur.fetchmany(size) 

        self.close()
        return self.count, self.res

    # 查询多条记录
    def select_many(self, sql, data1, size=1):
        self.connect()

        self.sql = sql
        self.cur.execute(self.sql, data1) 

        self.count = self.cur.rowcount  
        if size == 0:
            self.res = self.cur.fetchall() 
        elif size != 0:
            self.res = self.cur.fetchmany(size)  

        self.close()
        return self.count, self.res

    # 更新数据记录
    def update(self, sql, data1):
        self.connect()
        self.sql = sql

        try:
            self.cur.execute(self.sql, data1) 
            print('更新语句', self.sql, data1)
        except Exception:
            self.con.rollback()
            print('数据库运行出现异常！')

        self.close()

    # 删除数据记录
    def delete(self, sql, data1):
        self.connect()
        self.sql = sql

        try:
            print('删除语句', self.sql, data1)
            self.cur.execute(self.sql, data1)  
        except Exception:
            self.con.rollback()
            print('数据库运行出现异常！')

        self.close()

    # 插入一条数据记录
    def insert_one(self, sql, data1):
        self.connect()
        self.sql = sql

        try:
            print('插入语句', self.sql, data1)
            self.cur.execute(sql, data1)
        except Exception:
            self.con.rollback()
            print('数据库运行出现异常！')

        self.close()

    # 插入多条数据记录（数据部分以元组列表传入）
    def insert_many(self, sql, data1):
        self.connect()
        self.sql = sql

        try:
            self.cur.executemany(self.sql, data1)
            print('数据插入成功！')
            # print(str(len(data1) + "条数据已插入成功！"))
        except Exception:
            self.con.rollback()
            print('数据库运行出现异常！')

        self.close()

    # 运行 SQL 命令
    def excute_one(self, sql, data1):
        self.connect()
        self.sql = sql

        try:
            if self.sql.startswith('insert'):
                print('插入语句', self.sql, data1)
                self.cur.execute(self.sql, data1)

            elif self.sql.startswith('delete'):
                print('删除语句', self.sql, data1)
                self.cur.execute(self.sql, data1)

            elif self.sql.startswith('update'):
                print('更新语句', self.sql, data1)
                self.cur.execute(self.sql, data1)
        except Exception:
            self.con.rollback()
            print('数据库运行出现异常！')

        self.close()


# 调用
m = DataBase('192.168.43.230', 'root', '123456', 'testDB') # 建立数据库连接
print(m.select_all('select * from jiJi', 10))  # 查询结果
print(m.select_many('select * from test where name = "zhangsan"', 2))  # 查询结果
m.update('update test set name= %s where id = %s', ('zhang3', 1))  # 更新语句
m.delete('delete from test where id = %s', (1))  # 删除语句
m.insert_one('insert into test VALUES(%s, %s, %s)', (5, 'wangs5', 24))  # 插入语句
m.insert_many('insert into test VALUES(%s, %s, %s)',
             [(4, 'wang4', 54), (5, 'wangs5', 24), (6, 'wang6', 43)])  # 插入语句

二、读入 excel 并将其写入数据库

from MySqlHelp import DataBase
import xlrd

data = xlrd.open_workbook('3.xlsx')
sheet = data.sheet_by_index(3)
infore = []

class Student(object):
    pass

for i in range(sheet.nrows):
    if i > 2:
        student = Student()
        student.qiShu = sheet.cell(i, 0).value
        student.name = sheet.cell(i, 1).value
        student.sex = sheet.cell(i, 2).value
        student.minZu = sheet.cell(i, 3).value
        student.identy = sheet.cell(i, 4).value
        student.xueHao = sheet.cell(i, 5).value
        student.class1 = sheet.cell(i, 6).value
        student.jiGuan = sheet.cell(i, 7).value
        student.birth = sheet.cell(i, 8).value
        student.shenQingTime = sheet.cell(i, 9).value
        student.jiJiTime = sheet.cell(i, 10).value
        student.tel = sheet.cell(i, 11).value
        student.qq = sheet.cell(i, 12).value
        infore.append(student)

val = []
for i in infore:
    val.append((i.qiShu, i.name, i.sex, i.minZu, i.identy, 
    i.xueHao, i.class1, i.jiGuan, i.birth, i.shenQingTime, 
    i.jiJiTime, i.tel, i.qq))

# 创建数据库连接
db = DataBase('127.0.0.1', 'root', '123456', 'testDB')

# 创建数据表
sql = """create table jiJi(qiShu char(10), name char(20), 
		sex char(6), minZu char(10), identy char(20), 
		xuehao char(15) primary key, class1 char(16), 
		jiGuan char(16), birth char(12), shenQingTime char(12), 
		jiJiTime char(12), tel char(12), qq char(12))"""
db.create(sql)

# 将数据写入数据库
sql = '''insert into jiJi(qiShu, name, sex, minZu, identy,
		xuehao, class1, jiGuan, birth, shenQingTime, jiJiTime,
		tel ,qq) values
		(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
db.insert_many(sql, val)