我正在编写一个模块，使用 SQLAlchemy (SQLAlchemy==1.3.5) 和 Python 3.6.8 将内容记录到数据库中。
我尝试在表中添加日志。
如果表不存在，我会捕获"表不存在"错误，然后创建表(工作正常)。
问题是当我重试将同一项目添加到会话时它失败。似乎"提交"导致它停止了轨道。
我确实尝试使用'exc。NoSuchTableError"异常，但这似乎并没有发现该表不存在。我还尝试了仅使用刷新或仅提交的各种组合，但结果与输出相似。
我有一个单独的 test.py 文件，其中包含：
#!/usr/bin/env python
import ail
add_to_log = ail.add_to_log(
tool='ansible',
component='system management',
level=6,
description="This is a test message!",
)
ail.py 模块：
#!/usr/bin/env python
import sqlalchemy as db
from sqlalchemy import exc
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_filters import apply_filters
import pprint
import time
import re
def add_to_log(tool, component, description, level=6):
"""
Add an entry to the 'logs' table.
E.g: add_to_log = log.add_to_log(
tool='cims_import.py',
component='database',
level=6,
description="The CIMS import script was unable to access the CIMS database.",
)
:param tool: Tool the log item is for, e.g.: cims, spectrum, capm, nfa, etc.
:param component: The component the log relates to, e.g.: 'api', 'ssh' or 'backups'
:param level: 0=Emergency, 1=Alert, 2=Critical, 3=Error, 4=Warning, 5=Notice, 6=Informational, 7=Debug
:param description: Description of event added to the logs.
:return: dictionary containing results, e.g.: {'action': 'added'}
In the event of an error, a dict containing {'error': '<error_description>'} will be returned.
"""
try:
# Try to add log item to the 'logs' table:
logged_item = Log(
tool=tool,
component=component,
level=level,
description=description,
)
s2s.add(logged_item)
s2s.commit()
return {'action': 'added'}
except exc.SQLAlchemyError as e:
print("First attempt failed, creating database!")
# Unable to add the log item:
if re.search(r"Table '.*' doesn't exist", str(e)):
print("Table issue")
# The 'logs' table does not exist - try to create a new one:
try:
Base.metadata.tables["logs"].create(bind=s2)
except exc.SQLAlchemyError as e:
return {'error': e}
else:
print("Table success")
try:
print("Try add item again")
# Try to add the log item again:
logged_item = Log(
tool=tool,
component=component,
level=level,
description=description
)
print("going to add")
s2s.add(logged_item)
print("going to commit")
s2s.commit()
print("committed!")
return {'action': 'added'}
except exc.SQLAlchemyError as e:
return {'error': e}
else:
return {'error': e}
s2 = db.create_engine(
'mysql+mysqlconnector://user:password@server:port/s2db', pool_recycle=3600, echo=True)
session = sessionmaker()
session.configure(bind=s2)
s2s = session()
Base = declarative_base()
class Log(Base):
__tablename__ = 'logs'
id = db.Column(db.Integer(), primary_key=True, unique=True)
tool = db.Column(db.String(32), nullable=False)
component = db.Column(db.String(32), nullable=False)
level = db.Column(db.Integer(), default=0, nullable=False)
description = db.Column(db.String(256), nullable=False)
created = db.Column(db.DateTime(), server_default=db.func.now())
我确保数据库中不存在"日志"表并运行它：
第一次运行时的日志记录为：
(venv) [user@server venv]$ ./test.py
2019-07-11 23:40:32,608 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-07-11 23:40:32,608 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:40:32,611 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-07-11 23:40:32,611 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:40:32,613 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-07-11 23:40:32,613 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:40:32,614 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-07-11 23:40:32,614 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:40:32,615 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-07-11 23:40:32,615 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:40:32,616 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-07-11 23:40:32,618 INFO sqlalchemy.engine.base.Engine INSERT INTO logs (tool, component, level, description) VALUES (%(tool)s, %(component)s, %(level)s, %(description)s)
2019-07-11 23:40:32,618 INFO sqlalchemy.engine.base.Engine {'tool': 'ansible', 'component': 'system management', 'level': 6, 'description': 'This is a test message!'}
2019-07-11 23:40:32,628 INFO sqlalchemy.engine.base.Engine ROLLBACK
First attempt failed, creating database!
Table issue
2019-07-11 23:40:32,688 INFO sqlalchemy.engine.base.Engine
CREATE TABLE logs (
id INTEGER NOT NULL AUTO_INCREMENT,
tool VARCHAR(32) NOT NULL,
component VARCHAR(32) NOT NULL,
level INTEGER NOT NULL,
description VARCHAR(256) NOT NULL,
created DATETIME DEFAULT now(),
PRIMARY KEY (id),
UNIQUE (id)
)
2019-07-11 23:40:32,688 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:40:33,034 INFO sqlalchemy.engine.base.Engine COMMIT
Table success
Try add item again
going to add
going to commit
(venv) [user@server venv]$
那么为什么它不提交，甚至在"将要提交"之后放置其他错误消息呢？
因此，在此阶段，表本身已创建，但不存在日志条目。
如果我再次运行它(现在创建了表)它的任何次数都可以正常工作：
(venv) [user@server venv]$ test.py
2019-07-11 23:42:45,144 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-07-11 23:42:45,144 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:42:45,149 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-07-11 23:42:45,149 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:42:45,153 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-07-11 23:42:45,153 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:42:45,155 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-07-11 23:42:45,155 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:42:45,156 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-07-11 23:42:45,157 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:42:45,158 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-07-11 23:42:45,161 INFO sqlalchemy.engine.base.Engine INSERT INTO logs (tool, component, level, description) VALUES (%(tool)s, %(component)s, %(level)s, %(description)s)
2019-07-11 23:42:45,161 INFO sqlalchemy.engine.base.Engine {'tool': 'ansible', 'component': 'system management', 'level': 6, 'description': 'This is a test message!'}
2019-07-11 23:42:45,164 INFO sqlalchemy.engine.base.Engine COMMIT
(venv) [user@server venv]$
几乎就像表的创建干扰了会话 (s2s)。
任何人都可以就为什么会发生这种情况提供任何建议/帮助吗？
好的，所以我在 youtube 上看了一个视频，当他们提到回滚时，我认为我必须回滚，但似乎该会话并不意味着一个会话来统治它们(至少看起来它不像是 - 从我有限的知识来看 - 本周才开始使用 SQLAlchemy！
看起来最好创建和关闭会话。这似乎对我有用：
我删除了全局范围内的会话设置，只是将 2 个"将日志项添加到数据库"块替换为：
# NOTE: setup new session:
s2s = session()
logged_item = Log(
tool=tool,
component=component,
level=level,
description=description
)
print("going to add")
s2s.add(logged_item)
print("going to commit")
s2s.commit()
print("committed!")
# NOTE: close session:
s2s.close()
return {'action': 'added'}
结果：
(venv) [user@server venv]$ ./test.py
2019-07-11 23:58:44,259 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019-07-11 23:58:44,259 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:58:44,264 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019-07-11 23:58:44,264 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:58:44,268 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019-07-11 23:58:44,268 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:58:44,270 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019-07-11 23:58:44,270 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:58:44,272 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019-07-11 23:58:44,272 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:58:44,274 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-07-11 23:58:44,276 INFO sqlalchemy.engine.base.Engine INSERT INTO logs (tool, component, level, description) VALUES (%(tool)s, %(component)s, %(level)s, %(description)s)
2019-07-11 23:58:44,277 INFO sqlalchemy.engine.base.Engine {'tool': 'ansible', 'component': 'system management', 'level': 6, 'description': 'This is a test message!'}
2019-07-11 23:58:44,278 INFO sqlalchemy.engine.base.Engine ROLLBACK
First attempt failed, creating database!
Table issue
2019-07-11 23:58:44,334 INFO sqlalchemy.engine.base.Engine
CREATE TABLE logs (
id INTEGER NOT NULL AUTO_INCREMENT,
tool VARCHAR(32) NOT NULL,
component VARCHAR(32) NOT NULL,
level INTEGER NOT NULL,
description VARCHAR(256) NOT NULL,
created DATETIME DEFAULT now(),
PRIMARY KEY (id),
UNIQUE (id)
)
2019-07-11 23:58:44,334 INFO sqlalchemy.engine.base.Engine {}
2019-07-11 23:58:44,368 INFO sqlalchemy.engine.base.Engine COMMIT
Table success
Try add item again
going to add
going to commit
2019-07-11 23:58:44,370 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-07-11 23:58:44,370 INFO sqlalchemy.engine.base.Engine INSERT INTO logs (tool, component, level, description) VALUES (%(tool)s, %(component)s, %(level)s, %(description)s)
2019-07-11 23:58:44,371 INFO sqlalchemy.engine.base.Engine {'tool': 'ansible', 'component': 'system management', 'level': 6, 'description': 'This is a test message!'}
2019-07-11 23:58:44,374 INFO sqlalchemy.engine.base.Engine COMMIT
committed!
(venv) [user@server venv]$