def _test_insert(self, person_cls): Person = person_cls s = Session() s.bulk_insert_mappings(Person, [{"id": 5, "personname": "thename"}]) eq_(s.query(Person).first(), Person(id=5, personname="thename"))
def test_eval_none_flag_orm(self): from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import Session Base = declarative_base() class Data(Base): __table__ = self.tables.data_table s = Session(testing.db) d1 = Data(name='d1', data=None, nulldata=None) s.add(d1) s.commit() s.bulk_insert_mappings( Data, [{"name": "d2", "data": None, "nulldata": None}] ) eq_( s.query( cast(self.tables.data_table.c.data, String(convert_unicode="force")), cast(self.tables.data_table.c.nulldata, String) ).filter(self.tables.data_table.c.name == 'd1').first(), ("null", None) ) eq_( s.query( cast(self.tables.data_table.c.data, String(convert_unicode="force")), cast(self.tables.data_table.c.nulldata, String) ).filter(self.tables.data_table.c.name == 'd2').first(), ("null", None) )
def add_user(*, db: Session = Depends(deps.get_db), user: schemas.UserCreate) -> Any: """用户管理-新增""" user = user.dict() deptId = user.pop("deptId") postIds = user.pop("postIds") roleIds = user.pop("roleIds") # User add_user = models.User(**user) db.add(add_user) db.flush() # department user_department = { "user_id": add_user.id, "department_id": deptId, } db.add(models.User_Department(**user_department)) db.flush() # role user_roles = [{"user_id": add_user.id, "role_id": i} for i in roleIds] db.bulk_insert_mappings(models.User_Role, user_roles) db.flush() # dict # post user_post = [{"user_id": add_user.id, "dict_id": i} for i in postIds] user_dict = user_post + [] db.bulk_insert_mappings(models.User_Dict, user_dict) db.flush() return { "code": 20000, "message": "新增成功", }
def update_user(*, db: Session = Depends(deps.get_db), user: schemas.UserUpdate) -> Any: """用户管理-修改""" user_id = user.id user = user.dict() deptId = user.pop("deptId") postIds = user.pop("postIds") roleIds = user.pop("roleIds") # User db.query(models.User).filter(models.User.id == user_id).update(user) db.flush() # department db.query(models.User_Department).filter( models.User_Department.user_id == user_id).delete() user_department = {"user_id": user_id, "department_id": deptId} db.add(models.User_Department(**user_department)) db.flush() # role db.query( models.User_Role).filter(models.User_Role.user_id == user_id).delete() user_roles = [{"user_id": user_id, "role_id": i} for i in roleIds] db.bulk_insert_mappings(models.User_Role, user_roles) db.flush() # dict db.query( models.User_Dict).filter(models.User_Dict.user_id == user_id).delete() # post user_post = [{"user_id": user_id, "dict_id": i} for i in postIds] user_dict = user_post + [] db.bulk_insert_mappings(models.User_Dict, user_dict) db.flush() return {"code": 20000, "message": "修改成功"}
def table_check_insert(self): Base = declarative_base() Session = sessionmaker(bind=self.eng) session = Session() class ArbTimeSeries(Base): """this tells SQLAlchemy that rows of Basrah_WS_Base table must be mapped to this class""" __tablename__ = 'ArbTimeSeriesData' __table_args__ = {'extend_existing': True, 'schema': 'dbo'} Id = Column(Integer, primary_key=True) Date = Column(Date) Series = Column(String(32)) Value = Column(Numeric(12, 2)) Source = Column(String(32)) if not self.eng.dialect.has_table( self.eng, 'ArbTimeSeriesData', schema='dbo'): Base.metadata.create_all(self.eng) try: session.bulk_insert_mappings(ArbTimeSeries, self.timeseries_records) session.commit() session.close() except Exception as e: print(e) session.rollback() session.close()
def test_bulk_insert_render_nulls(self): Order, = self.classes("Order") s = Session() with self.sql_execution_asserter() as asserter: s.bulk_insert_mappings( Order, [ {"id": 1, "description": "u1new"}, {"id": 2, "description": None}, {"id": 3, "description": "u3new"}, ], render_nulls=True, ) asserter.assert_( CompiledSQL( "INSERT INTO orders (id, description) " "VALUES (:id, :description)", [ {"id": 1, "description": "u1new"}, {"id": 2, "description": None}, {"id": 3, "description": "u3new"}, ], ) )
def insert_many(): service = ReviewDfo() Session = openSession() session = Session() df = service.hook() print(df.head()) session.bulk_insert_mappings(ReviewDto, df.to_dict(orient="records")) session.commit() session.close()
def test_bulk_insert_dictionaries(n): """Individual INSERT/COMMIT pairs using the "bulk" API with dictionaries""" for i in range(n): session = Session(bind=engine) session.bulk_insert_mappings(Customer, [ dict( name='customer name %d' % i, description='customer description %d' % i )]) session.commit()
def test_bulk_insert_mappings(n): """Batched INSERT statements via the ORM "bulk", using dictionaries.""" session = Session(bind=engine) session.bulk_insert_mappings(Customer, [ dict( name='customer name %d' % i, description='customer description %d' % i ) for i in range(n) ]) session.commit()
def test_bulk_insert_joined_inh_return_defaults(self): Person, Engineer, Manager, Boss = \ self.classes('Person', 'Engineer', 'Manager', 'Boss') s = Session() with self.sql_execution_asserter() as asserter: s.bulk_insert_mappings( Boss, [ dict( name='b1', status='s1', manager_name='mn1', golf_swing='g1' ), dict( name='b2', status='s2', manager_name='mn2', golf_swing='g2' ), dict( name='b3', status='s3', manager_name='mn3', golf_swing='g3' ), ], return_defaults=True ) asserter.assert_( CompiledSQL( "INSERT INTO people (name) VALUES (:name)", [{'name': 'b1'}] ), CompiledSQL( "INSERT INTO people (name) VALUES (:name)", [{'name': 'b2'}] ), CompiledSQL( "INSERT INTO people (name) VALUES (:name)", [{'name': 'b3'}] ), CompiledSQL( "INSERT INTO managers (person_id, status, manager_name) " "VALUES (:person_id, :status, :manager_name)", [{'person_id': 1, 'status': 's1', 'manager_name': 'mn1'}, {'person_id': 2, 'status': 's2', 'manager_name': 'mn2'}, {'person_id': 3, 'status': 's3', 'manager_name': 'mn3'}] ), CompiledSQL( "INSERT INTO boss (boss_id, golf_swing) VALUES " "(:boss_id, :golf_swing)", [{'golf_swing': 'g1', 'boss_id': 1}, {'golf_swing': 'g2', 'boss_id': 2}, {'golf_swing': 'g3', 'boss_id': 3}] ) )
def _test_insert(self, person_cls): Person = person_cls s = Session() with self.sql_execution_asserter(testing.db) as asserter: s.bulk_insert_mappings( Person, [{"id": 5, "personname": "thename"}] ) eq_(s.query(Person).first(), Person(id=5, personname="thename")) return asserter
def _test_insert(self, person_cls): Person = person_cls s = Session() s.bulk_insert_mappings( Person, [{"id": 5, "personname": "thename"}] ) eq_( s.query(Person).first(), Person(id=5, personname="thename") )
def setup_database(dburl, echo, num): global engine engine = create_engine(dburl, echo=echo) Base.metadata.drop_all(engine) Base.metadata.create_all(engine) s = Session(engine) for chunk in range(0, num, 10000): s.bulk_insert_mappings(Customer, [ { 'name': 'customer name %d' % i, 'description': 'customer description %d' % i } for i in range(chunk, chunk + 10000) ]) s.commit()
def test_bulk_insert(self): User, = self.classes("User",) s = Session() with self.sql_execution_asserter() as asserter: s.bulk_insert_mappings( User, [{'id': 1, 'name': 'u1new'}, {'id': 2, 'name': 'u2'}, {'id': 3, 'name': 'u3new'}] ) asserter.assert_( CompiledSQL( "INSERT INTO users (id, name) VALUES (:id, :name)", [{'id': 1, 'name': 'u1new'}, {'id': 2, 'name': 'u2'}, {'id': 3, 'name': 'u3new'}] ) )
def test_bulk_insert_render_nulls(self): Order, = self.classes("Order",) s = Session() with self.sql_execution_asserter() as asserter: s.bulk_insert_mappings( Order, [{'id': 1, 'description': 'u1new'}, {'id': 2, 'description': None}, {'id': 3, 'description': 'u3new'}], render_nulls=True ) asserter.assert_( CompiledSQL( "INSERT INTO orders (id, description) VALUES (:id, :description)", [{'id': 1, 'description': 'u1new'}, {'id': 2, 'description': None}, {'id': 3, 'description': 'u3new'}] ) )
def test_bulk_insert(self): User, = self.classes("User") s = Session() with self.sql_execution_asserter() as asserter: s.bulk_insert_mappings( User, [ {"id": 1, "name": "u1new"}, {"id": 2, "name": "u2"}, {"id": 3, "name": "u3new"}, ], ) asserter.assert_( CompiledSQL( "INSERT INTO users (id, name) VALUES (:id, :name)", [ {"id": 1, "name": "u1new"}, {"id": 2, "name": "u2"}, {"id": 3, "name": "u3new"}, ], ) )
def test_bulk_insert_joined_inh_return_defaults(self): Person, Engineer, Manager, Boss = self.classes( "Person", "Engineer", "Manager", "Boss" ) s = Session() with self.sql_execution_asserter() as asserter: s.bulk_insert_mappings( Boss, [ dict( name="b1", status="s1", manager_name="mn1", golf_swing="g1", ), dict( name="b2", status="s2", manager_name="mn2", golf_swing="g2", ), dict( name="b3", status="s3", manager_name="mn3", golf_swing="g3", ), ], return_defaults=True, ) asserter.assert_( CompiledSQL( "INSERT INTO people (name) VALUES (:name)", [{"name": "b1"}] ), CompiledSQL( "INSERT INTO people (name) VALUES (:name)", [{"name": "b2"}] ), CompiledSQL( "INSERT INTO people (name) VALUES (:name)", [{"name": "b3"}] ), CompiledSQL( "INSERT INTO managers (person_id, status, manager_name) " "VALUES (:person_id, :status, :manager_name)", [ {"person_id": 1, "status": "s1", "manager_name": "mn1"}, {"person_id": 2, "status": "s2", "manager_name": "mn2"}, {"person_id": 3, "status": "s3", "manager_name": "mn3"}, ], ), CompiledSQL( "INSERT INTO boss (boss_id, golf_swing) VALUES " "(:boss_id, :golf_swing)", [ {"golf_swing": "g1", "boss_id": 1}, {"golf_swing": "g2", "boss_id": 2}, {"golf_swing": "g3", "boss_id": 3}, ], ), )