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_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},
],
),
)
