sqlalchemy ORM进阶－批量插入数据

参考：

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

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

1 import pandas as pd
2 import sqlalchemy
3 
4 df = pd.DataFrame()
5 print(df)

结果：

1 Empty DataFrame
2 Columns: []
3 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()

 1 import pandas as pd
 2 import sqlalchemy
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column,String,Integer,Float,TIMESTAMP

 8 
 9 from demo.demogetConnection import GetConnection
10 
11 session = GetConnection.session
12 engine =GetConnection.engine
13 
14 Base = declarative_base()
15 class User(Base):
16     __tablename__="user"
17     id = Column(Integer,primary_key=True)
18     name = Column(String(45))
19 
20     def __repr__(self):
21         return "<User(id='%s',name='%s')>"%(self.id,self.name)
22 
23 
24 
25 df = pd.DataFrame({"name":["Jon","Mary","Tom"]})
26 df.to_sql("user",con=engine,if_exists="append",index=False)

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

结果：

第2种方法，使用for循环

 1 import sqlalchemy
 2 from sqlalchemy.ext.declarative import declarative_base
 3 from sqlalchemy import Column,String,Integer,Float,TIMESTAMP
 4 
 5 from demo.demogetConnection import GetConnection
 6 
 7 session = GetConnection.session
 8 engine =GetConnection.engine
 9 
10 Base = declarative_base()
11 class User(Base):
12     __tablename__="user"
13     id = Column(Integer,primary_key=True)
14     name = Column(String(45))
15 
16     def __repr__(self):
17         return "<User(id='%s',name='%s')>"%(self.id,self.name)
18 
19 
20 #原生方法，批量插入
21 a=[]
22 for i in range(3):
23     a.append({"name":"曹操%s"%i})
24 
25 print(a)
26 session.execute(User.__table__.insert(),a)
27 session.commit()

打印的a的结果：

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

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

sqlalchemy ORM进阶－ 批量插入数据

sqlalchemy ORM进阶－批量插入数据