数据库—Python操作之Sqlalchemy库

sqlalchemy批量插入数据库

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String
from db import session

Base = declarative_base()


class User(Base):
    __tablename__="user"
    id = Column(Integer,primary_key=True)
    name_en = Column(String(32))

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

user_list = []
for i in range(3):
     user_list.append({"id": "1c687b8f86ac42e981a235aa9da0d66a", "name": "name%s" % i})
     
 # 方式一：原生sql的方式批量插入
session.execute(User.__table__.insert(), user_list)
# 同样直接传入sql也可以
session.execute("INSERT INTO tb_name (id, name) VALUES ("1c687b8f86ac42e981a235aa9da0d66a", "jack"), ("5b60a71c0b294e13a8316422b170d4cd", "william"), (...)")

session.commit()

# 方式二：bulk_save_objects
objects = [
   User(**user) for user in user_list
]
s.bulk_save_objects(objects)
s.commit()