我的home目录中有一个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和熊猫连接时，某事不起作用。
db=sqla.create_engine('sqlite:////home/stephen/db1')
pd.read_sql('select * from db1.test',db)
~/anaconda3/lib/python3。7/站点包/sqlalchemy/engine/default。py in do_execute（self，cursor，statement，parameters，context）578 579 def do_execute（self，cursor，statement，parameters，context=None）：--
数据库错误：（sqlite3.数据库错误）文件不是数据库[SQL：选择*从db1.test]（此错误的背景：http://sqlalche.me/e/4xp6）
我还尝试：
db=sqla.create_engine('sqlite:///~/db1')
同样的结果
正如Everila所指出的，问题在于没有向后兼容性。anaconda安装自己的sqlite，即sqlite3。并且sqlite无法加载由sqlite 2创建的数据库。使用sqlite 3创建db后，代码工作正常
db=sqla.create_engine('sqlite:////home/stephen/db1')
pd.read_sql('select * from test',db)
这确认了需要4个斜杠。
就个人而言，只是为了完成@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()
请让我知道这是否有帮助！
