Connecting Google Sheets & Streamlit

Using Streamlit
1

Hi,

I am trying to create an app in which I will ask users for a specific input (an answer), and if it is correct, I want to display a clue acorrdingly. To do this, I thought to use streamlit + google sheets to store the data

It should be pretty simple and i followed the guide to connect a public google sheet, but falling into this error…
ImportError: cannot import name ‘Iterable’ from ‘collections’ (C:\Program Files\Python310\lib\collections_init_.py)

Traceback:

File "C:\Users\nina.vistisen\PycharmProjects\treasureHunt\venv\lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 556, in _run_script
    exec(code, module.__dict__)File "C:\Users\nina.vistisen\PycharmProjects\treasureHunt\main.py", line 2, in <module>
    from gsheetsdb import connectFile "C:\Users\nina.vistisen\PycharmProjects\treasureHunt\venv\lib\site-packages\gsheetsdb\__init__.py", line 1, in <module>
    from gsheetsdb.db import connectFile "C:\Users\nina.vistisen\PycharmProjects\treasureHunt\venv\lib\site-packages\gsheetsdb\db.py", line 11, in <module>
    from gsheetsdb.query import executeFile "C:\Users\nina.vistisen\PycharmProjects\treasureHunt\venv\lib\site-packages\gsheetsdb\query.py", line 11, in <module>
    from moz_sql_parser import parse as parse_sqlFile "C:\Users\nina.vistisen\PycharmProjects\treasureHunt\venv\lib\site-packages\moz_sql_parser\__init__.py", line 15, in <module>
    from moz_sql_parser.sql_parser import SQLParser, scrub_literal, scrubFile "C:\Users\nina.vistisen\PycharmProjects\treasureHunt\venv\lib\site-packages\moz_sql_parser\sql_parser.py", line 13, in <module>
    from mo_parsing.helpers import delimitedList, restOfLineFile "C:\Users\nina.vistisen\PycharmProjects\treasureHunt\venv\lib\site-packages\mo_parsing\__init__.py", line 60, in <module>
    from mo_parsing.infix import LEFT_ASSOC, RIGHT_ASSOC, infixNotationFile "C:\Users\nina.vistisen\PycharmProjects\treasureHunt\venv\lib\site-packages\mo_parsing\infix.py", line 4, in <module>
    from collections import Iterable

I have used the code from the guide but with links to my public worksheet:

import streamlit as st
from gsheetsdb import connect

# Create a connection object.
conn = connect()

# Perform SQL query on the Google Sheet.
# Uses st.cache to only rerun when the query changes or after 10 min.
@st.cache(ttl=600)
def run_query(query):
    rows = conn.execute(query, headers=1)
    rows = rows.fetchall()
    return rows

sheet_url = st.secrets["public_gsheets_url"]
rows = run_query(f'SELECT * FROM "{sheet_url}"')

# Print results.
for row in rows:
    st.write(f"{row.name} has a :{row.pet}:")

Any help would be really appreciated. I’m pretty new to this!

2

Hi @NinaVistisen,

Have you tried running this with Python 3.9 instead?

Caroline

3

From the project description in PyPi:

This package is deprecated. Use shillelagh (GitHub - betodealmeida/shillelagh: Making it easy to query APIs via SQL) instead, which is a drop-in replacement.

1 Like
4

Hi @Caroline,

This worked a treat, thanks for the help!

1 Like
5

Hi everyone.
I was looking into the shillelagh package but for some reason im getting this error:
st.write(f"{row.string} is :{row.measure}")
AttributeError: ‘tuple’ object has no attribute ‘string’

So its not outputting how it normally outputted when gsheetsdb was working.

my code:

streamlit_app.py

import streamlit as st
#from gsheetsdb import connect
from shillelagh.backends.apsw.db import connect

Create a connection object.

conn = connect(“:memory:”)
cursor = conn.cursor()

Perform SQL query on the Google Sheet.

Uses st.cache to only rerun when the query changes or after 10 min.

@st.cache(ttl=600)
def run_query(query):
rows = cursor.execute(query)
rows = rows.fetchall()
return rows

sheet_url = st.secrets[“public_gsheets_url”]
rows = run_query(f’SELECT * FROM “{sheet_url}”')

query = f’SELECT * FROM “{sheet_url}”’

for row in cursor.execute(query):

st.write(row)

Print results.

for row in rows:
st.write(f"{row.string} is :{row.measure}")

How do I return without the tuples? Sorry in advance im a complete noob to programming.

6

do
st.write(f"{row[0]} is :{row[1]}")
this is assuming the first element in your tuple is ‘string’ and the second is ‘measure’

7

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.