一、从pg数据导出到xlsx代码实现
python处理表格小试牛刀 附上代码(基于flask_sqlalchemy)
from io import BytesIO
from app import db
from app.models import User
import openpyxl
from app import db
class User(db.Model):
__tablename__ = 'tb_user'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
name = db.Column(db.String(64), unique=True, name='name', doc='名字')
email = db.Column(db.String(128), name='email', doc='邮箱')
age = db.Column(db.Integer, name='age', doc='年龄')
def __repr__(self):
return '({},{},{},{})'.format(self.id, self.name, self.email, self.age)
if __name__ == '__main__':
db.create_all()
def export_data():
records = db.session.query(User).all()
print(records)
title=['序号','姓名','邮箱','年龄']
sheet_records=[]
for record in records:
sheet_records.append([
record.id,record.name,record.email,record.age
])
wb=openpyxl.Workbook()
wb.encoding='gbk'
sheet = wb.active
sheet.title = "个人信息"
for col in range(len(title)):
c = col +1
sheet.cell(row=1,column=c).value=title[col]
for row in range(len(sheet_records)):
sheet.append(sheet_records[row])
wb.save("./personal.xlsx")
wb.save("./data/data.xlsx")
sio = BytesIO()
wb.save(sio)
print(sio.getvalue())
sio.close()
if __name__ == '__main__':
export_data()
执行效果
1.数据库的数据截图
2.导出后的数据截图
二、从xlsx导入到pg代码实现
将上一步的数据导出的表格重新导入到pg中,重新建立模型类和数据表
from openpyxl import load_workbook
from app import db
class Message(db.Model):
__tablename__ = 'tb_messqge'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
order = db.Column(db.Integer, name='order', doc='序号')
email = db.Column(db.String(128), name='email', doc='邮箱')
name = db.Column(db.String(128), name='name', doc='年龄')
age = db.Column(db.Integer, name='age', doc='年龄')
def __repr__(self):
return '({},{},{},{})'.format(self.id, self.name, self.email, self.age)
def xlsx_to_pg(localpath):
wb = load_workbook(localpath)
ws = wb[wb.sheetnames[0]]
rows = ws.max_row
columns = ws.max_column
column_heading = [ws.cell(row=1, column=x).value for x in range(1, columns + 1)]
column_name = ['序号', '姓名', '邮箱', '年龄']
print(column_heading)
if len([name for name in column_name if name in column_heading]) == len(column_name):
order = column_heading.index(column_name[0])
name = column_heading.index(column_name[1])
email = column_heading.index(column_name[2])
age = column_heading.index(column_name[3])
print(order,name,email,age)
if ws.cell(row=2, column=1).value == None:
table_start_line = 3
else:
table_start_line = 2
data = []
MessageList = []
for row in range(table_start_line, rows + 1):
for column in range(1, columns + 1):
data.append(ws.cell(row=row, column=column).value)
print(data)
print(data[order], data[name], data[email], data[age])
MessageList.append(Message(order=data[order], name=data[name], email=data[email], age=data[age]))
data = []
try:
db.session.add_all(MessageList)
db.session.commit()
msg = '导入成功'
return msg
except Exception as e:
print('导入出错{}'.format(e))
msg = '导入失败'
return msg
finally:
wb.close()
return "Success"
if __name__ == '__main__':
xlsx_to_pg("./data.xlsx")
执行效果
1.数据库中的数据截图