如何使用SQLAlchemy连接SQLite

Question

我在我的主目录中有一个sqlite数据库。

stephen@stephen-AO725:~$ pwd
/home/stephen
stephen@stephen-AO725:~$ sqlite db1
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> select * from test
   ...> ;
3|4
5|6
sqlite> .quit

当我尝试使用SQLAlchemy和Pandas从Jupyter笔记本连接时，有些东西无法正常工作。

db=sqla.create_engine('sqlite:////home/stephen/db1')
pd.read_sql('select * from db1.test',db)

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py中的do_execute函数在执行SQL语句时发生了错误:

DatabaseError: (sqlite3.DatabaseError) file is not a database [SQL: select * from db1.test] (更多信息请参考该错误的背景网页：http://sqlalche.me/e/4xp6)

我还尝试过：

db=sqla.create_engine('sqlite:///~/db1')

相同的结果

- Stephen

---

URL应该有三个斜杠，而不是四个。 - Klaus D.

@klaus 4 是正确的，如果使用绝对路径。sqlite://，后跟不存在的主机，然后是分隔符 /，最后是路径：https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#connect-strings - Ilja Everilä

看到控制台输出的 SQLite version 2.8.17 引起了注意。我认为 Python 驱动程序是针对 SQLite 3.x 的，因此可能无法打开您的数据库文件。 - Ilja Everilä

根据帮助页面，URL 应该有 4 条斜杠。 - Stephen

Answer 1

就我个人而言，只是为了完成@Stephen的代码，并使用所需的模块：

# 1.-Load module
import sqlalchemy
import pandas as pd
#2.-Turn on database engine
dbEngine=sqlalchemy.create_engine('sqlite:////home/stephen/db1.db') # ensure this is the correct path for the sqlite file. 

#3.- Read data with pandas
pd.read_sql('select * from test',dbEngine)

#4.- I also want to add a new table from a dataframe in sqlite (a small one) 

df_todb.to_sql(name = 'newTable',con= dbEngine, index=False, if_exists='replace') 

另一种阅读的方法是使用sqlite3库，这可能更加直接：

#1. - Load libraries
import sqlite3
import pandas as pd

# 2.- Create your connection.
cnx = sqlite3.connect('sqlite:////home/stephen/db1.db')
cursor = cnx.cursor()

# 3.- Query and print all the tables in the database engine
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())

# 4.-  READ TABLE OF SQLITE CALLED test
dfN_check = pd.read_sql_query("SELECT * FROM test", cnx) # we need real name of table

#  5.- Now I want to delete all rows of this table
cnx.execute("DELETE FROM test;")

# 6. -COMMIT CHANGES! (mandatory if you want to save these changes in the database)
cnx.commit()


# 7.- Close the connection with the database
cnx.close()

请告诉我这是否有帮助！

- Corina Roca

Answer 2

import sqlalchemy

engine=sqlalchemy.create_engine(f'sqlite:///db1.db')

注意：在使用相对路径时，需要在“sqlite:///”中使用三个斜杠来指定数据库路径。
如果需要使用绝对路径，则需要使用四个斜杠：“sqlite:////”。 来源：链接

- Avazbek Vaxobov

Answer 3

正如Everila所指出的那样，问题在于缺乏向后兼容性。Anaconda安装了自己的SQLite，即SQLite3.x，而该SQLite无法加载由SQLite 2.x创建的数据库。在使用SQLite 3创建数据库后，代码可以正常工作。

db=sqla.create_engine('sqlite:////home/stephen/db1')
pd.read_sql('select * from test',db)

这证实了需要四个斜杠。

- Stephen

Answer 4

对于我使用的Python 3.10.6和SQLAlchemy 2.0.0b4，没有任何SQLAlchemy解决方案适用于我，这可能是一个beta问题或版本2.0.0改变了一些东西。@corina-roca的解决方案很接近，但不正确，因为您需要传递连接对象而不是引擎对象。这就是文档所说的，但实际上并没有起作用。经过一些实验，我发现engine.raw_connect()可以工作，尽管在CLI上会收到警告。以下是我的工作示例

sqlite的一个可以直接使用 - 但如果您想更改数据库，则不是理想的选择

import sqlite3

conn = sqlite3.connect("sqlite:////home/stephen/db1")
df = pd.read_sql_query('SELECT * FROM test', conn)
df.head()

# works, no problem

SQLAlchemy让你可以将数据库抽象化

from sqlalchemy import create_engine, text

engine = create_engine("sqlite:////home/stephen/db1")
conn = engine.connect() # <- this is also what you are supposed to 
                        #    pass to pandas... it doesn't work
result = conn.execute(text("select * from test"))
for row in result:
    print(row)          # outside pands, this works - proving that
                        # connection is established
    
conn = engine.raw_connection() # with this workaround, it works; but you
                               # get a warning UserWarning: pandas only 
                               # supports SQLAlchemy connectable ...
df = pd.read_sql_query(sql='SELECT * FROM test', con=conn) 
df.head()

- gotofritz