获取空的 sqlite DB 和 "(sqlite3.OperationalError) no such table:..:"尝试添加项目时 [英] Getting empty sqlite DB and "(sqlite3.OperationalError) no such table:..:" when trying to add item
问题描述
我写了一个通用的dbhandler模块，可以纠缠数据容器，上传到mySQL数据库，独立于DB结构.现在我想添加一个默认值或将数据推送到 sqlite 数据库的可能性.从结构上讲，这与这个问题有关.包看起来像这样:
I wrote a general dbhandler module that can entangle data containers and uploade them to a mySQL database and is independent of the DB structure. Now I want to add a default or the possibility to shove the data into a sqlite DB. Structure-wise this is related to this question. The package looks like this:
dbhandler\
dbhandler.py
models\
meta.py
default\
default_DB_map.py
default_DB.cfg
default.cfg 是描述 dbhandler 脚本的数据库的配置文件.default_DB_map.py 包含 DB 的每个表的映射，它继承自 BASE:
default.cfg is the config file that describes the database for the dbhandler script. default_DB_map.py contains a map for each table of the DB, which inherits from BASE:
from sqlalchemy import BigInteger, Column, Integer, String, Float, DateTime
from sqlalchemy import Date, Enum
from ..meta import BASE
class db_info(BASE):
__tablename__ = "info"
id = Column(Integer, primary_key=True)
name = Column(String)
project = Column(String)
manufacturer = Column(String)
...
class db_probe(BASE):
__tablename__ = "probe"
probeid = Column(Integer, primary_key=True)
id = Column(Integer)
paraX = Column(String)
...
在 meta.py 中，我初始化 declarative_base 对象:
In meta.py I initialize the declarative_base object:
from sqlalchemy.ext.declarative import declarative_base
BASE = declarative_base()
最后，我在 dbhandler.py 中导入 BASE 并创建引擎和会话:
And eventually, I import BASE within the dbhandler.py and create the engine and session:
"DBHandler module"
...
import sqlalchemy
from sqlalchemy.orm import sessionmaker
from models import meta #pylint: disable=E0401
....
class DBHandler(object):
"""Database handling
Methods:
- get_dict: returns table row
- add_item: adds dict to DB table
- get_table_keys: gets list of all DB table keys
- get_values: returns all values of key in DB table
- check_for_value: checks if value is in DB table or not
- upload: uploads data container to DB
- get_dbt: returns DBTable object
"""
def __init__(self, db_cfg=None):
"""Load credentials, DB structure and name of DB map from cfg file,
create DB session. Create DBTable object to get table names of DB
from cfg file, import table classes and get name of primary keys.
Args:
- db_cfg (yaml) : contains infos about DB structure and location
of DB credentials.
Misc:
- cred = {"host" : "...",
"database" : "...",
"user" : "...",
"passwd" : "..."}
"""
...
db_cfg = self.load_cfg(db_cfg)
if db_cfg["engine"] == "sqlite":
engine = sqlalchemy.create_engine("sqlite:///mySQlite.db")
meta.BASE.metadata.create_all(engine)
session = sessionmaker(bind=engine)
self.session = session()
elif db_cfg["engine"] == "mysql+mysqlconnector":
cred = self.load_cred(db_cfg["credentials"])
engine = sqlalchemy.create_engine(db_cfg["engine"]
+ "://"
+ cred["user"] + ":"
+ cred["passwd"] + "@"
+ cred["host"] + ":"
+ "3306" + "/"
+ cred["database"])
session = sessionmaker(bind=engine)
self.session = session()
else:
self.log.warning("Unkown engine in DB cfg...")
# here I'm importing the table classes stated in the config file
self.dbt = DBTable(map_file=db_cfg["map"],
table_dict=db_cfg["tables"],
cr_dict=db_cfg["cross-reference"])
我显然在 if db_cfg["engine"] == "sqlite": 段落中做错了什么，但我不知道是什么.
I'm obviously doing something wrong within the if db_cfg["engine"] == "sqlite": paragraph, but I can't figure out what.
该脚本在 mySQL 引擎上运行良好.当我初始化处理程序对象时，我得到一个空的 mySQLite.db 文件.添加该会话的内容会产生:
The script is working just fine with the mySQL engine. When I initialize the handler object I'm getting an empty mySQLite.db file.
Adding something with that session yields:
(sqlite3.OperationalError) no such table: info....
但是，我可以在表对象上使用sqlalchemy.inspect"之类的东西而不会出现任何错误.所以我手头有正确的表对象，但它们不知何故没有连接到底座?
I can however use something like ´sqlalchemy.inspect´ on a table object without any errors. So I have the correct table objects at hand, but they are somehow not connected to the base?
推荐答案
对于 SQLite，显然需要在创建数据库之前导入表类.
For SQLite, apperently the import of the table classes needs to happen before the DB is created.
# here I'm importing the table classes stated in the config file
self.dbt = DBTable(map_file=db_cfg["map"],
table_dict=db_cfg["tables"],
cr_dict=db_cfg["cross-reference"])
(这是通过 pydoc.locate btw 完成的)必须在
(which is done via pydoc.locate btw) has to be done before
engine = sqlalchemy.create_engine("sqlite:///mySQlite.db")
meta.BASE.metadata.create_all(engine)
session = sessionmaker(bind=engine)
self.session = session()
被调用.我认为这并不重要，因为我在开始时导入了 BASE，并且在使用不同的引擎时它工作得很好.
is called. I thought this was not important since I imported BASE at the beginning and since it works just fine when using a different engine.
这篇关于获取空的 sqlite DB 和 "(sqlite3.OperationalError) no such table:..:"尝试添加项目时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
