python操作excel表格

python操作excel表格

通过Visual Studio Code软件，使用python操作excel表格或mysql数据库

把excel表格的数据读入python

import encodings
from openpyxl.reader.excel import load_workbook
import json
import openpyxl

# 读取excel2007文件
#wb = load_workbook(filename=r'/Users/holmes/Visual_Studio_Code/data/my.xlsx')
wb = openpyxl.load_workbook('/Users/holmes/Visual_Studio_Code/data/my.xlsx')
# 显示有多少张工作表
print(wb.get_sheet_names())
# 取第一张工作表
#ws = wb.sheetnames[0]
ws = wb.get_sheet_by_name(wb.sheetnames[0])
# 显示表名，表行数，表列数
print(ws)
print("行数：",ws.max_row)
print("列数：",ws.max_column)
# 建立存储数据的字典
data_dic = {}
# 把数据存到字典中
for rx in range(1, ws.max_row+1):
    temp_list = []
    pid = rx
    w1 = ws.cell(row=rx, column=1).value
    w2 = ws.cell(row=rx, column=2).value
    w3 = ws.cell(row=rx, column=3).value
    w4 = ws.cell(row=rx, column=4).value
    temp_list = [w1, w2, w3, w4]
    data_dic[pid] = temp_list
# 打印字典数据个数
print('total:%d' % len(data_dic))
#打印字典
print(data_dic)

把excel表格的数据存入mysql数据库

import encodings
from openpyxl.reader.excel import load_workbook
import json
import openpyxl
import pymysql


# 打开数据库连接
conn = pymysql.connect(host="127.0.0.1", user="root",
                       password="1234", charset="utf8")
# 选择连接的数据库
conn.select_db('liyangwei')
# print(conn)
# 获取数据库游标
cursor = conn.cursor()  # 游标用来接受返回结果

# 创建licanbin数据库，在终端查看数据库命令mysql> show databases;
#cursor.execute('CREATE DATABASE IF NOT EXISTS liyangwei DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')

# 创建user表，创建数据库的数据存放格式，按照excel表格行列格式
cursor.execute('DROP TABLE IF EXISTS user')  # 如果存在表user就删除
cursor.execute('drop table if exists user')  # 创建表user
sql = """CREATE TABLE IF NOT EXISTS `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `age` int(11) NOT NULL,
   PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
cursor.execute(sql)
sql = "insert into user values(%s,%s,%s)"


#################################################################
# 读取excel2007文件
#wb = load_workbook(filename=r'/Users/holmes/Visual_Studio_Code/data/my2.xlsx')
wb = openpyxl.load_workbook('/Users/holmes/Visual_Studio_Code/data/my2.xlsx')
# 显示有多少张工作表
print(wb.get_sheet_names())
# 取第一张工作表
#ws = wb.sheetnames[0]
ws = wb.get_sheet_by_name(wb.sheetnames[0])
# 显示表名，表行数，表列数
print(ws)
print("行数：", ws.max_row)
print("列数：", ws.max_column)
# 建立存储数据的字典
#data_dic = {}

# 把数据存到数据库中
for rx in range(1, ws.max_row+1):
    w1 = ws.cell(row=rx, column=1).value
    w2 = ws.cell(row=rx, column=2).value
    w3 = ws.cell(row=rx, column=3).value
    # 把数据写进入数据库
    cursor.execute(sql, (w1, w2, w3))


# 查询数据库所有数据
cursor.execute("select * from user;")
while 1:
    res = cursor.fetchone()
    if res is None:
        # 表示已经取完结果集
        break
    print(res)
cursor.close()
conn.commit()
conn.close()

python数据写入excel

import pymysql
import datetime  # python类型会被自动转换
from openpyxl import Workbook
from openpyxl.compat import *
from openpyxl.utils import get_column_letter

sheet_name = 'building'
sql = 'select * from excel'
# 打开数据库连接
conn = pymysql.connect(host="127.0.0.1", user="root",
                       password="1234", charset="utf8")
# 选择连接的数据库
conn.select_db('licanbin')
# 获取数据库游标
cursor = conn.cursor()
# 创建工作博
wb = Workbook()
# 创建工作表sheet
ws1 = wb.active
ws1.title = "sheet1"
ws2 = wb.create_sheet(title="sheet2")
ws3 = wb.create_sheet(title="|sheet3")
# 循环添加数据
for row in range(1, 40):
    ws1.append(range(600))

for row in range(10, 20):
    for col in range(27, 54):
        _ = ws3.cell(column=col, row=row,
                     value="{0}".format(get_column_letter(col)))
print(ws3['AA10'].value)
# 数据可以直接分配到单元格中
ws2['A1'] = 42
ws2.cell(row=4, column=2, value='test')
ws2.cell(row=5, column=2).value = "liyangwei"
# 可以附加行，从第一列开始附加
ws2.append([1, 2, 3])
ws2['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")
# 保存文件
wb.save("/Users/holmes/Visual_Studio_Code/data/my.xlsx")

mysql数据库写入数据进入excel

import pymysql
from openpyxl import Workbook

conn = pymysql.connect(host="127.0.0.1", user="root",
                       password="1234", charset="utf8")
# 选择连接的数据库
conn.select_db('licanbin')
# print(conn)
# 获取数据库游标
cursor = conn.cursor()
cursor.execute("select * from user")

# 取所有数据
resTuple = cursor.fetchall()
print(type(resTuple))
print('还剩%d条数据' % len(resTuple))
print(resTuple)
# 查询一条数据
# print(cursor.fetchone())
# 创建工作博
wb = Workbook()
# 创建工作表sheet
ws1 = wb.active
ws1.title = "sheet1"
ws2 = wb.create_sheet(title="sheet2")
ws3 = wb.create_sheet(title="|sheet3")
cursor.execute("select * from user")
# 保存数据库中的数据到excel表格中
while 1:
    res = cursor.fetchone()  # 逐行获取数据库中的数据
    if res is None:
        # 表示已经取完结果集
        break
    ws1.append(res)
#    print(res)
# 保存文件
wb.save("/Users/holmes/Visual_Studio_Code/data/my2.xlsx")

cursor.close()
conn.commit()
conn.close()

mysql数据库插入数据

import string
import random
import pymysql

#打开数据库连接
conn = pymysql.connect(host="127.0.0.1", user="root",
                       password="1234", charset="utf8")
# 选择连接的数据库
conn.select_db('licanbin')
# print(conn)
# 获取数据库游标
cursor = conn.cursor()  # 游标用来接受返回结果

# 创建licanbin数据库，在终端查看数据库命令mysql> show databases;
#cursor.execute('CREATE DATABASE IF NOT EXISTS liyangwei DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')

# 创建user表
cursor.execute('DROP TABLE IF EXISTS user') #如果存在表user就删除
cursor.execute('drop table if exists user') #创建表user
sql = """CREATE TABLE IF NOT EXISTS `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `age` int(11) NOT NULL,
   PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
cursor.execute(sql)
# 添加数据
#insert = cursor.execute("insert into user values(1, 'tom', 18)")
#print('添加语句受影响的行数：', insert)

# 添加数据 通过字符串传入值
#sql = "insert into user values(%s,%s,%s)"
#insert = cursor.executemany(
#    sql, [(4, 'wen', 20), (5, 'tom', 10), (6, 'test', 30)])
#print('批量插入返回受影响的行数：', insert)

#修改前查询所有数据
cursor.execute("select * from user;")
print('修改前的数据为：')
for res in cursor.fetchall():
    print(res)
print('*'*40)

# 批量插入100条数据
words=list(string.ascii_letters)
sql="insert into user values(%s,%s,%s)"
for i in range(100):
    random.shuffle(words)#打乱顺序
    cursor.execute(sql,(i+1,"".join(words[:5]),random.randint(0,80)))


# 查询所有数据
cursor.execute("select * from user;")
while 1:
    res = cursor.fetchone()
    if res is None:
        # 表示已经取完结果集
        break
    print(res)
cursor.close()
conn.commit()
conn.close()
# print(cursor)

mysql数据库删除数据

import pymysql
#打开数据库连接
conn = pymysql.connect(host="127.0.0.1", user="root",
                       password="1234", charset="utf8")
conn.select_db('licanbin')
#获取游标
cur=conn.cursor()


#删除前查询所有数据
cur.execute("select * from user")
print('删除前的数据为：')
for res in cur.fetchall():
      print (res)

print ('*'*40)      
#删除1条数据
#cur.execute("delete from user where id=1")
#删除2条数据
sql="delete from user where id=%s"
cur.executemany(sql,[(3),(4)])
#回滚事务 恢复删除的数据
#conn.rollback()
#cur.execute("select * from user;")
#删除后查询所有数据
cur.execute("select * from user")
print('删除后的数据为：')
for res in cur.fetchall():
      print (res)
cur.close()
conn.commit()
conn.close()
print('sql执行成功')

连接mysql数据库

from turtle import update
import pymysql
conn = pymysql.connect(host="127.0.0.1", user="root",
                       password="1234", charset="utf8")
# 选择连接的数据库
conn.select_db('licanbin')
# print(conn)
# 获取数据库游标
cursor = conn.cursor()
cursor.execute("select * from user")

# 取3条数据
# resTuple=cursor.fetchmany(3)
# print(type(resTuple))
# for res in resTuple:
#  print(res)
# 取所有数据
# resTuple=cursor.fetchall()
# print(type(resTuple))
# print('还剩%d条数据'%len(resTuple))
#查询一条数据
#print(cursor.fetchone())

#更新一条数据
update=cursor.execute("update user set age=100 where name='tom'")
cursor.execute("select * from user")
#更新2条数据
sql="update user set age=%s where name=%s"
update=cursor.executemany(sql,[(15,'tom'),(18,'test')])
cursor.execute("select * from user")
# 查询数据
while 1:
    res = cursor.fetchone()
    if res is None:
        # 表示已经取完结果集
        break
    print(res)
cursor.close()
conn.commit()
conn.close()