SqlAlchemy

[douglasoliveiraadv]

How to connect with SqlAlchemy?

I am trying to url below in Apache Superset but it is not working:

gsheets://gsheets://https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/edit#gid=0

Returns the following error:

superset_1 | 2019-10-09 17:28:18,959:ERROR:root:invalid literal for int() with base 10: ''
superset_1 | Traceback (most recent call last):
superset_1 | File "/home/superset/superset/views/core.py", line 1701, in testconn
superset_1 | database.set_sqlalchemy_uri(uri)
superset_1 | File "/home/superset/superset/models/core.py", line 836, in set_sqlalchemy_uri
superset_1 | conn = sqla.engine.url.make_url(uri.strip())
superset_1 | File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/url.py", line 229, in make_url
superset_1 | return _parse_rfc1738_args(name_or_url)
superset_1 | File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/url.py", line 288, in _parse_rfc1738_args
superset_1 | return URL(name, **components)
superset_1 | File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/url.py", line 71, in init
superset_1 | self.port = int(port)
superset_1 | ValueError: invalid literal for int() with base 10: ''

From the command line this works:

root@e87274843f8f:/home/superset# gsheetsdb
sql> select * from "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8";
country cnt

---

BR 1
BR 3
IN 5
ZA 6

I await help. Thanks!

[eugeniamz]

This works in Preset ( hosted version of Superset ) so the drivers may not be installed properly

[JagritiG]

I have Installed package 'gsheetsdb' (version=0.1.12) and other dependencies from the requirement.txt file. Then, I have run the following code in PyCharm:

---

from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *

# Connection and authentication
service_account_file = "service_account_file.json"    # your service account credential file
engine = create_engine('gsheets://', service_account_file=service_account_file, subject='[email protected]')
inspector = inspect(engine)
url = "google_spread_sheet_url"

# Processing
table = Table(
    url,
    MetaData(bind=engine),
    autoload=True)

query = select([func.count(table.columns.cnt)], from_obj=table)
print(query.scalar())

---

Note: This works for me