CODEWITHSUNDEEP
pythonsqlitesqlalchemy
Stephen
Stephen

Reputation: 614

how to connect to sqlite from sqlalchemy

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

Upvotes: 12

Views: 58770

Answers (4)

gotofritz
gotofritz

Reputation: 3381

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

Upvotes: 0

Avazbek Vaxobov
Avazbek Vaxobov

Reputation: 101

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

Upvotes: 10

Corina Roca
Corina Roca

Reputation: 546

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!

Upvotes: 18

Stephen
Stephen

Reputation: 614

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.

Upvotes: 2

Related Questions