使用SQLAlchemy engin将数据帧保存到SQLite3 DB

Question

我正在使用Pandas to \u sql（）方法将API获取的一些外汇数据保存到SQLite3数据库中，但由于某些原因，它不想插入数据库中，我尝试使用纯SQLite3，我可以让它工作。但一旦我使用SQLAlchemy作为引擎，我就无法插入它。我正在使用一个上下文管理器，我认为它应该处理连接的关闭和提交，但我仍然没有运气：

def insert(self, data: pd.DataFrame):
    engine = create_engine("sqlite:///{}".format("'C:\\Users\\Dirk Dirksen\\PycharmProjects\\FoxexBot\\forex.db'"))
    with engine.connect() as connection:
        connection.execute("CREATE TABLE IF NOT EXISTS {} "
                           "(pair TEXT NOT NULL, price FLOAT NOT NULL, time INTEGER NOT NULL);".format(self.index))
        data.to_sql(name="forex.db", con=connection, index=False, if_exists='append'

那个连接.执行（SQL）只是创建表，如果它不在那里，真正的问题是MyDataframe.to\u sql（）这是应该填充我的表的内容。我得到的错误如下：

"C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\python.exe" "C:/Users/Dirk Dirksen/PycharmProjects/FoxexBot/forex_server.py"
{'USDCAD': {'rate': 1.31535, 'timestamp': 1576978687}, 'USDJPY': {'rate': 109.44904, 'timestamp': 1576978687}, 'EURUSD': {'rate': 1.108, 'timestamp': 1576987745}, 'EURGBP': {'rate': 0.852013, 'timestamp': 1576987745}, 'NZDUSD': {'rate': 0.660004, 'timestamp': 1576987745}}
Traceback (most recent call last):
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2276, in _wrap_pool_connect
    return fn()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 492, in checkout
    rec = pool._do_get()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\impl.py", line 238, in _do_get
    return self._create_connection()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlite3.OperationalError: unable to open database file

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:/Users/Dirk Dirksen/PycharmProjects/FoxexBot/forex_server.py", line 70, in <module>
    fill_forex_data_to_db()
  File "C:/Users/Dirk Dirksen/PycharmProjects/FoxexBot/forex_server.py", line 62, in fill_forex_data_to_db
    db.insert(data=pairs)
  File "C:/Users/Dirk Dirksen/PycharmProjects/FoxexBot/forex_server.py", line 49, in insert
    with engine.connect() as connection:
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2209, in connect
    return self._connection_cls(self, **kwargs)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 103, in __init__
    else engine.raw_connection()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2306, in raw_connection
    return self._wrap_pool_connect(
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2279, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1547, in _handle_dbapi_exception_noconnection
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2276, in _wrap_pool_connect
    return fn()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 492, in checkout
    rec = pool._do_get()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\impl.py", line 238, in _do_get
    return self._create_connection()
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\pool\base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\Dirk Dirksen\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file
(Background on this error at: http://sqlalche.me/e/e3q8)

它说它无法打开该文件，但我打开该文件的唯一时间是在调试时，它仍然给我这个错误，所以我不认为这是一个锁定问题。这使我相信这可能是一个错误放置变量的问题。 目前，我的项目布局如下所示：

我打印表格内容的方法如下。这似乎可行，但不断打印空表：

def select_all(self):
    con = sqlite3.connect(database=self.db_path)
    return pd.read_sql_query("SELECT * FROM {}".format(self.index), con=con)

我哪里出错了？它是to\u sql（）方法吗？你知道吗

Answer 1

如前所述，应删除路径中的单引号。
另外，to_sql()中的name应该指向表名，而不是文档中的文件名 https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

完整代码（删除self并使用“my\u table”作为表名）

def insert(data: pd.DataFrame):
    engine = create_engine("sqlite:///{}".format("C:\\Users\\Dirk Dirksen\\PycharmProjects\\FoxexBot\\forex.db"))
    with engine.connect() as connection:
        connection.execute("CREATE TABLE IF NOT EXISTS {} "
                           "(pair TEXT NOT NULL, price FLOAT NOT NULL, time INTEGER NOT NULL);".format('my_table'))
        data.to_sql(name='my_table', con=connection, index=False, if_exists='append')

d = pd.DataFrame({
    'pair': ['abc'],
    'price': [1.2],
    'time': [123],
})

insert(d)