how to connect to sqlite from sqlalchemy

Question

I have a sqlite db in my home dir.

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

when I try to connect from a jupiter notebook with sqlalchemy and pandas, sth does not work.

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 in do_execute(self, cursor, statement, parameters, context) 578 579 def do_execute(self, cursor, statement, parameters, context=None): --> 580 cursor.execute(statement, parameters) 581 582 def do_execute_no_params(self, cursor, statement, context=None):

DatabaseError: (sqlite3.DatabaseError) file is not a database [SQL: select * from db1.test] (Background on this error at: http://sqlalche.me/e/4xp6)

I also tried:

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

same result

@klaus 4 is correct, if using absolute paths. `sqlite://`, followed by non-existent host, then the separating `/`, and finally the path: https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#connect-strings — Ilja Everilä, Nov 17 '19 at 07:00
Looking at the console output `SQLite version 2.8.17` stuck out. I think the Python driver is for SQLite 3.x, and so may not be able to open your database file. — Ilja Everilä, Nov 17 '19 at 10:48

score 12 · Answer 1 · answered Feb 10 '21 at 16:04

Personally, just to complete the code of @Stephen with the modules required:

# 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')

Another way to read is using sqlite3 library, which may be more straighforward:

#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()

Please let me know if this helps!

score 6 · Answer 2 · answered Jan 24 '22 at 13:08

6

import sqlalchemy

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

Note: that you need three slashes in sqlite:/// in order to use a relative path for the DB.
If you want an absolute path, use four slashes: sqlite://// Source: Link

answered Jan 24 '22 at 13:08

Avazbek Vaxobov

61
1
6

score 2 · Answer 3 · answered Dec 06 '19 at 21:32

The issue is no backward compatibility as noted by Everila. anaconda installs its own sqlite, which is sqlite3.x and that sqlite cannot load databases created by sqlite 2.x after creating a db with sqlite 3 the code works fine

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

which confirms the 4 slashes are needed.

score 0 · Answer 4 · answered Dec 24 '22 at 22:36

None of the sqlalchemy solutions worked for me with python 3.10.6 and sqlalchemy 2.0.0b4, it could be a beta issue or version 2.0.0 changed things. @corina-roca's solution was close, but not right as you need to pass a connection object, not an engine object. That's what the documentation says, but it didn't actually work. After a bit of experimentation, I discovered that engine.raw_connect() works, although you get a warning on the CLI. Here are my working examples

The sqlite one works out of the box - but it's not ideal if you are thinking of changing databases later

import sqlite3

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

# works, no problem

sqlalchemy lets you abstract your db away

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()

how to connect to sqlite from sqlalchemy

4 Answers4