MetaData.__init__() got an unexpected keyword argument 'bind' when using sqlite

Question

im very new to sqlite, im using macbook m1, im try to use jupyter notebook to do sql here is my code

%load_ext sql

import csv, sqlite3

con = sqlite3.connect("socioeconomic.db")
cur = con.cursor()

everything is okay until this but when i connect to the sqlite

%sql sqlite:///socioeconomic.db

there is error something like this

MetaData.__init__() got an unexpected keyword argument 'bind'
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

do you have any suggestion what should i do?

i tried to use other laptop but windows, and the same code works, but in mac isnt

Balboa

Answer 1

Check out this guide! Essentially there are 2 ways you can connect from your Jupyter notebook to SQLite local db. I don't think it relates to your Mac version as I have M2 and it's running fine.

Check you have the .db file locally. Also, check if the same sqlalchemy versions are matching. I believe pip install sqlalchemy==1.4.4 should work. Another reason to move to Jupysql is that it supports the latest SQLAlchemy version.

Here are 2 ways you can make it run via Jupysql:

1. You can pass your existing connections to %sql as follows:

some_engine = create_engine("sqlite:///some.db")
%sql some_engine

2. You can connect directly to it:

%sql sqlite:///foo.db

For both options you'll need to set up the correct package and import it via %load_ext sql

Endurable

wow big thanks, after i tried to install install jupysql duckdb-engine it works somehow – Balboa

That's awesome, feel free to accept the answer :) You can also join our community, we'd love to hear your use case! ploomber.io/community – Endurable

Confirmed using jupysql fixed the issue, it's the fork of iphyton-sql. Command to install on conda conda install jupysql -c conda-forge (from their docs) – Pathless

Answer 2

What I did was to simply downgrade the SQLalchemy version. Mine was 2.0, so I rolled it back to version 1.4.39. To do this, head on to the environment tab, select and apply version 1.4.39. Make sure to restart the kernel.

Bah

Answer 3

De-versioning worked for me. Sqlalchemy v2.0 --> v1.4.4

Junie

Answer 4

The ability to bind engines or connections to metadata objects was removed in SQLAlchemy 2.0 (or 1.4 with future=True set on the engine). See “Implicit” and “Connectionless” execution, “bound metadata” removed. Instead, the engine of connection may be passed to methods that require it:

engine = create_engine(...)

metadata = MetaData()
metadata.drop_all(engine)
metadata.create_all(engine)
metadata.reflect(engine)

This and other breaking changes in 2.0 are documented in

• SQLAlchemy 2.0 Major Migration Guide
• What's New in SQLAlchemy 2.0

Miniver