def test_transaction(caplog: _pytest.logging.LogCaptureFixture,
logger: gluetool.log.ContextAdapter, db: DB) -> None:
"""
Test whether :py:func:`transaction` behaves correctly when wrapping non-conflicting queries.
"""
with db.get_session(transactional=True) as session:
update = tft.artemis.tasks._guest_state_update_query(
'dummy-guest',
GuestState.PROVISIONING,
current_state=GuestState.ROUTING).unwrap()
insert = sqlalchemy.insert(
GuestEvent.__table__).values( # type: ignore[attr-defined]
updated=datetime.datetime.utcnow(),
guestname='dummy-guest',
eventname='dummy-event')
with transaction() as r:
session.execute(update)
session.execute(insert)
assert r.success is True
requests = SafeQuery.from_session(session, GuestRequest).all().unwrap()
assert len(requests) == 1
# TODO: cast shouldn't be needed, sqlalchemy should annouce .state as enum - maybe with more recent stubs?
assert cast(GuestState, requests[0].state) == GuestState.PROVISIONING
events = SafeQuery.from_session(session, GuestEvent).all().unwrap()
assert len(events) == 1
assert events[0].guestname == 'dummy-guest'
assert events[0].eventname == 'dummy-event'
def test_transaction_no_transactions(
caplog: _pytest.logging.LogCaptureFixture,
logger: gluetool.log.ContextAdapter,
session: sqlalchemy.orm.session.Session) -> None:
"""
Test whether :py:func:`transaction` behaves correctly when facing non-transactional session.
"""
query1 = sqlalchemy.insert(Counters.__table__).values(name='counter1',
count=1)
query2 = sqlalchemy.insert(Counters.__table__).values(name='counter2',
count=2)
with transaction() as r:
session.execute(query1)
session.execute(query2)
assert r.success is True
records = SafeQuery.from_session(session, Counters).order_by(
Counters.name).all().unwrap()
assert len(records) == 2
assert records[0].name == 'counter1'
assert records[0].count == 1
assert records[1].name == 'counter2'
assert records[1].count == 2
def get(self, request, pk):
min = request.GET.get('min')
max = request.GET.get('max')
try:
try:
#参数校验 ,符合条件查询指定范围,不符合条件查询全部
min = int(min)
max = int(max)
if (not min <= max) or (min < 1) or (max < 1):
raise Exception
except Exception as e:
#查询全部排行
cursor = session.execute(
'select t.score,t.client_code,(select count(s.score)+1 from user_ranking s where s.score>t.score) rank from user_ranking t order by t.score desc;',
)
else:
#查询指定范围排行
cursor = session.execute(
'select t.score,t.client_code,(select count(s.score)+1 from user_ranking s where s.score>t.score) rank from user_ranking t order by t.score desc limit :min,:max;',
params={
"min": min - 1,
'max': (max - min) + 1
})
#排行列表
rank_list = []
ranks = cursor.fetchall()
#把查询出来的排行拼接成字典
for rank in ranks:
rank_list.append({
'score': rank[0],
'cid': rank[1],
'no': rank[2],
})
#查询当前客户端排行,并添加到字典
cursor = session.execute(
'select t.score,t.client_code,(select count(s.score)+1 from user_ranking s where s.score>t.score) rank from user_ranking t where t.client_code=1;'
)
result = cursor.fetchall()
rank_list.append({
'score': result[0][0],
'cid': result[0][1],
'no': result[0][2]
})
except exc.DatabaseError as e:
logger.error(e)
return http.JsonResponse({
'code': RETCODE.DBERR,
'errmsg': '数据库操失败'
})
return http.JsonResponse({
'code': RETCODE.OK,
'errmsg': 'OK',
'rank_list': rank_list
})
async def get_issue(request: Request,
user_id: int,
session: session = Depends(get_db)):
data = await request.form()
session.execute(
update(UserTable).where(UserTable.id == user_id).values(**UserUpdate(
**data).dict(exclude_unset=True)))
session.commit()
return RedirectResponse(urljoin(str(request.base_url), "user"),
status_code=status.HTTP_303_SEE_OTHER)
def main(): # 主函数
engine = sqlalchemy.create_engine(MYSQL_URL, encoding="utf8",
echo=True) # 数据库引擎
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 创建Session
session = sqlalchemy.orm.session.Session()
session.execute(User.__table__.insert(),
[{
"name": "小花%s号" % random.randint(1, 999),
"note": "iii"
} for item in range(100000)])
session.commit()
session.close()
def funcion_create(funcion):
query = """INSERT INTO `car_db`.`funcion`
(`fecha_creacion`,`fecha_modificacion`,`nombre`,`fechaInicio`,`horaInicio`,`id_pelicula`,`id_formato`,`id_sala`)
VALUES(CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'{nombre}','{fechaInicio}','{horaInicio}',{peliculaId},{formatoId},{salaId})
""".format(
nombre=funcion['nombre'],
fechaInicio=
f"{funcion['fechaAnio']}-{funcion['fechaMes']}-{funcion['fechaDia']}",
horaInicio=f"{funcion['hora']}:{funcion['minuto']}:00",
peliculaId=funcion['peliculaId'],
formatoId=funcion['formatoId'],
salaId=funcion['salaId'])
session.execute(query)
session.commit()
def main(): # 主函数
engine = sqlalchemy.create_engine(MYSQL_URL, encoding="utf8",
echo=True) # 数据库引擎
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 创建Session
session = sqlalchemy.orm.session.Session()
result = session.execute(SQL, [{"start": 0, "size": 20}])
for row in result.fetchall():
print(row)
session.close()
def main():
try:
engine = sqlalchemy.create_engine(MYSQL_URL, echo=True) # 返回所有的操作信息
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(bind=engine) # 創建Session類型
session = sqlalchemy.orm.session.Session() # 實例化Session對象
result = session.execute(SQL, {"uid": 1}) # 執行SQL處理
logging.info(f"刪除的數據行數:{result}") # 獲得更新行數
session.commit() # 提交更新
session.close() # 關畢session（釋放連接）
except Exception as e:
logging.info(traceback.format_exc())
def main():
engine = sqlalchemy.create_engine(MYSQL_URL, encoding='UTF8', echo=True)
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(bind=engine) # 创建sesstion类型
session = sqlalchemy.orm.session.Session()
result=session.execute(SQL,{"uid":1})
print('删除的行数%s'%result.rowcount)
session.commit()
session.close()
session.close()
def compra(funcionId, tickets, email, nombre):
print(tickets)
query = """INSERT INTO `car_db`.`compra`
(`fecha_creacion`,`fecha_modificacion`,`email`,`id_funcion`,`nombre`)
VALUES(CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'{email}',{funcionId},'{nombre}')
""".format(email=email, funcionId=funcionId, nombre=nombre)
session.execute(query)
compra = session.query(Compra).order_by(
Compra.fecha_creacion.desc()).filter(~Compra.tickets.any()).filter(
Compra.email == email).filter(
Compra.id_funcion == funcionId).first()
for t in tickets:
session.execute("""INSERT INTO `car_db`.`compra_ticket`
(`id_compra`,`id_ticket`)
VALUES({idCompra},{idTicket})
""".format(idCompra=compra.id, idTicket=t.id))
session.commit()
session.flush()
return compra.id
session.expire(compra)
session.refresh(compra)
session.expire_all()
def main():
try:
engine = sqlalchemy.create_engine(MYSQL_URL, echo=True) # 返回所有的操作信息
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 創建Session類型
session = sqlalchemy.orm.session.Session() # 實例化Session對象
result = session.execute(SQL, [{"start": 0, "size": 20}]) # 執行SQL處理
logging.info(f"數據行數:{result.rowcount}")
for row in result.fetchall():
# logging.info(type(row))
logging.info(row)
logging.info(f"--------------------------------------------")
SQL1 = f"SELECT uid,name,note FROM user LIMIT {0}, {20}" # 查詢語句
result = session.execute(SQL1) # 執行SQL處理
logging.info(f"數據行數:{result.rowcount}")
for row in result:
logging.info(row)
# session.commit() # 提交事務
session.close() # 關畢session（釋放連接）
except Exception as e:
logging.info(traceback.format_exc())
def main():
try:
engine = sqlalchemy.create_engine(MYSQL_URL, echo=True) # 返回所有的操作信息
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 創建Session類型
session = sqlalchemy.orm.session.Session() # 實例化Session對象
# result = session.execute(
# User.__table__.insert(), # 內部生成的SQL語句
# [{"name": "[YA]沐言優拓 -%s" % random.randint(1, 999),
# "note": "www.example.com"} for item in range(100000)]
# ) # 執行SQL處理
result = session.execute(
User.__table__.insert(), # 內部生成的SQL語句
[{
"name": f"[YA]沐言優拓 -{random.randint(1, 999)}",
"note": "www.example.com"
} for item in range(100000)]) # 執行SQL處理
session.commit() # 提交更新
session.close() # 關畢session（釋放連接）
except Exception as e:
logging.info(traceback.format_exc())
def set_id(self, session):
[[self.id]] = session.execute("select nextval('%s_id_seq')" %
self.__tablename__)
def funcion_cancel(id):
session.execute(
f"UPDATE `car_db`.`funcion` SET cancelada = 1 WHERE funcion.id = {id} "
)
session.commit()
from models import Student
from sqlalchemy import engine
from sqlalchemy.orm import session
from connection import Connection
from sqlalchemy.sql.schema import MetaData
from sqlalchemy.sql import table, insert
engine = Connection.get_connection()
session = Connection.create_session()
metadata = MetaData(bind=engine)
student = table("student", metadata, autoload=True)
i = insert(student)
i = i.values({"name": "mahdi", "family": "kaseb", "grade": 99})
session.execute(i)