1、连接数据库及相关操作代码如下:
import pymysql
import xlrd
class MysqlConnect(object):
# 魔术方法, 初始化, 构造函数
def __init__(self, localhost, user, password, database):
'''
:param localhost: IP
:param user: 用户名
:param password: 密码
:param port: 端口号
:param database: 数据库名
:param charset: 编码格式
'''
self.db = pymysql.connect(host=localhost, user=user, password=password,port=3306, database=database, charset='utf8')
self.cursor = self.db.cursor()
# 将要插入的数据写成元组传入
def exec_data(self, sql, data=None):
# 执行SQL语句
self.cursor.execute(sql, data)
# 提交到数据库执行
self.db.commit()
# sql拼接时使用repr(),将字符串原样输出
def exec(self, sql):
self.cursor.execute(sql)
# 提交到数据库执行
self.db.commit()
# 插入多条数据
def insert_many(self, sql, datas):
self.cursor.executemany(sql, datas)
self.db.commit()
def select(self,sql):
self.cursor.execute(sql)
# 获取所有记录列表
results = self.cursor.fetchall()
for row in results:
print(row)
# 魔术方法, 析构化 ,析构函数
def __del__(self):
self.cursor.close()
self.db.close()
if __name__ == '__main__':
mc = MysqlConnect('127.0.0.1', 'root', '123456', 'math')
# mc.exec('insert into user(username, password) values(%s, %s)' % (repr('张三'), repr('123456')))
# mc.exec_data('update user set username = %s, password = %s where id = 3', ('李四', '123456'))
# mc.exec_data('insert into user(id, username) values(%s, %s)',(3, '马云'))
# mc.exec_data('delete from user where id = %d' % 4)
# mc.exec_data('delete from user where 1 = 1 ')
#打开Excel表格,一次性将内容insert到数据库中
workbook = xlrd.open_workbook(r'C:\\Users\\liangguiming\\Desktop\\1.xlsx')
sheet = workbook.sheet_by_index(0)
dataArr = []
for i in range(5):
rows = sheet.row_values(i)
dataArr.append(rows)
#插入多条数据格式
# manyData = [['5', '6'], ['1', '2'], ['3', '4']]
sql = 'insert into user(username, password) values(%s, %s)'
# print(dataArr)
mc.insert_many(sql, dataArr)
mc.select('select * from user')
2、Excel表格内容如下:
3、操作完成后,数据库中数据如下显示
4、在console中打印的数据如下所示