sqlalchemy ORM进阶－ 批量插入数据

参考：

https://www.jb51.net/article/49789.htm

https://blog.csdn.net/littlely_ll/article/details/82706874

 import pandas as pd

 import sqlalchemy

 df = pd.DataFrame()

 print(df)

结果：

 Empty DataFrame

 Columns: []

 Index: []

插入1条数据

import sqlalchemy

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,String,Integer,Float,TIMESTAMP

from demo.demogetConnection import GetConnection

session = GetConnection.session

# engine =GetConnection.engine

Base = declarative_base()

class User(Base):

    __tablename__="user"

    id = Column(Integer,primary_key=True)

    name = Column(String(45))

    def __repr__(self):

        return "<User(id='%s',name='%s')>"%(self.id,self.name)

#插入单条数据：

new_user = User(name='lily')

#添加数据，但还没有提交，出错还可以使用rollback撤回操作

session.add(new_user)

#提交到数据，这一步才是真正的将数据插入到数据库中了

session.commit()

结果

利用pandas批量插入数据

方式一：使用pandas_obj.to_sql()

 import pandas as pd

 import sqlalchemy

 from sqlalchemy.ext.declarative import declarative_base

 from sqlalchemy import Column,String,Integer,Float,TIMESTAMP

 from demo.demogetConnection import GetConnection

 session = GetConnection.session

 engine =GetConnection.engine

 Base = declarative_base()

 class User(Base):

     __tablename__="user"

     id = Column(Integer,primary_key=True)

     name = Column(String(45))

     def __repr__(self):

         return "<User(id='%s',name='%s')>"%(self.id,self.name)

 df = pd.DataFrame({"name":["Jon","Mary","Tom"]})

 df.to_sql("user",con=engine,if_exists="append",index=False)

在使用to_sql时注意if_exists参数，如果是replace的话它会先drop掉表，然后再创建表，最后插入数据

结果：

第2种方法，使用for循环

 import sqlalchemy

 from sqlalchemy.ext.declarative import declarative_base

 from sqlalchemy import Column,String,Integer,Float,TIMESTAMP

 from demo.demogetConnection import GetConnection

 session = GetConnection.session

 engine =GetConnection.engine

 Base = declarative_base()

 class User(Base):

     __tablename__="user"

     id = Column(Integer,primary_key=True)

     name = Column(String(45))

     def __repr__(self):

         return "<User(id='%s',name='%s')>"%(self.id,self.name)

 #原生方法，批量插入

 a=[]

 for i in range(3):

     a.append({"name":"曹操%s"%i})

 print(a)

 session.execute(User.__table__.insert(),a)

 session.commit()

打印的a的结果：

[{'name': '曹操0'}, {'name': '曹操1'}, {'name': '曹操2'}]

查询表的结果，已经插入成功了：