SQLAlchemy简单入门-不是小白入门
废话不多说直接上代码,大神勿喷,小白勿喷,给有缘人看的
import os
# import sys
from sqlalchemy import Column, String, create_engine,Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import pandas as pd
Base = declarative_base()
def read_csv_from_file_to_list(file_path):
"""从路径读取csv文件 编码默认为utf8 返回list
"""
file_name = os.path.basename(file_path)
file_path = os.path.dirname(file_path)
data = pd.read_csv(os.path.join(file_path,file_name))
csv_list = data.values.tolist()
return csv_list
class Xml_map(Base):
__tablename__ = 'xml_map'
id = Column(Integer, primary_key=True,autoincrement=True)
image_name = Column(String(250), nullable=True)
image_path = Column(String(250), nullable=True)
image_width = Column(String(250), nullable=True)
image_height = Column(String(250), nullable=True)
xml_name = Column(String(250), nullable=True)
object_name = Column(String(250), nullable=True)
x0 = Column(String(250), nullable=True)
y0 = Column(String(250), nullable=True)
x1 = Column(String(250), nullable=True)
y1 = Column(String(250), nullable=True)
wd = Column(String(250), nullable=True)
ht = Column(String(250), nullable=True)
aspect = Column(String(250), nullable=True)
version = Column(String(250), nullable=True)
scenes = Column(String(250), nullable=True)
# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/test_sqlalchemy')
"""初始化创建数据库中的表"""
# Base.metadata.drop_all(engine)
# Base.metadata.create_all(engine)
"""这里只创建一次所以注释"""
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
session = DBSession()
csv_list = read_csv_from_file_to_list(file_path=r'test.csv')
# [image_name, image_width , image_height, object_name, x0, y0, x1, y1, version, xml_path]
for one_item in csv_list:
# 创建新User对象:
# new_user = Xml_map(id='5', name='Bob')
item = Xml_map(image_name=one_item[0],
image_width=one_item[1],
image_height=one_item[2],
image_path="",
xml_name='',
object_name=one_item[3],
x0=one_item[4],
y0=one_item[5],
x1=one_item[6],
y1=one_item[7])
# 添加到session:
session.add(item)
del item
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.close()
#