Error with st.experimental_connections

[DrBenjamin]

Hi everyone,
I am trying to set up the st.experimental_connections to a MySQL database. To read from the database works, but writing always give the Error:

ArgumentError: Textual SQL expression ‘INSERT INTO document (ID,…’ should be explicitly declared as text(‘INSERT INTO document (ID,…’)

My Code

connection = st.experimental_connection(name = 'ghost', type = 'sql', autocommit = True)
with connection.session as session:
    session.execute('INSERT INTO document (ID, SENTENCE) VALUES(:id, :sen);', params = dict(id = id, sen = answer))
    session.commit()

[tonykip]

@DrBenjamin

I am trying to set up the st.experimental_connections to a MySQL database. To read from the database works, but writing always give the Error:

ArgumentError: Textual SQL expression ‘INSERT INTO document (ID,…’ should be explicitly declared as text(‘INSERT INTO document (ID,…’)

My Code

connection = st.experimental_connection(name = 'ghost', type = 'sql', autocommit = True)
with connection.session as session:
    session.execute('INSERT INTO document (ID, SENTENCE) VALUES(:id, :sen);', params = dict(id = id, sen = answer))
    session.commit()

Can you share the repo? That will help us troubleshoot this for you.

[DrBenjamin]

Hi Tony,

you will find it here:

GitHub - DrBenjamin/Ghostwriter

I am testing with a local MySQL database. Error appears on Mac and Windows.

[tonykip]

Thank you for the link to your repo. I noticed you’re using this:

params = dict(id = id, sen = answer)

Try passing the dictionary directly as the second argument like this:

with connection.session as session:
    session.execute('INSERT INTO document (ID, SENTENCE) VALUES(:id, :sen);', {"id": id, "sen": answer})
    session.commit()

Let me know if this works.

[DrBenjamin]

@tonykip

session.execute('INSERT INTO document (ID, SENTENCE) VALUES(:id, :sen);', {"id": id, "sen": answer})

Thanks, unfortunately I already tried this before - same error.

[tonykip]

Can you paste the entire error you’re getting from your terminal/logs here?

[DrBenjamin]

2023-09-29 16:51:43.113 Uncaught app exception
Traceback (most recent call last):
File “/Users/ben/miniconda3/lib/python3.10/site-packages/streamlit/runtime/scriptrunner/script_runner.py”, line 541, in _run_script
exec(code, module.dict)
File “/Users/ben/Library/Mobile Documents/com~apple~CloudDocs/Python/Streamlit/Ghostwriter/ghost.py”, line 115, in
session.execute(‘INSERT INTO document (ID, SENTENCE) VALUES(:id, :sen);’, {“id”: id, “sen”: answer})
File “/Users/ben/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py”, line 2262, in execute
return self._execute_internal(
File “/Users/ben/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py”, line 2042, in _execute_internal
statement = coercions.expect(roles.StatementRole, statement)
File “/Users/ben/miniconda3/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py”, line 413, in expect
resolved = impl._literal_coercion(
File “/Users/ben/miniconda3/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py”, line 638, in _literal_coercion
return self._text_coercion(element, argname, **kw)
File “/Users/ben/miniconda3/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py”, line 631, in _text_coercion
return _no_text_coercion(element, argname)
File “/Users/ben/miniconda3/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py”, line 601, in _no_text_coercion
raise exc_cls(
sqlalchemy.exc.ArgumentError: Textual SQL expression ‘INSERT INTO document (ID,…’ should be explicitly declared as text(‘INSERT INTO document (ID,…’)

[DrBenjamin]

session.execute('CREATE TABLE IF NOT EXISTS `document` (ID INT, SENTENCE TEXT);')
session.execute('DELETE FROM `ghostwriter`.`document`;')

produce the same error.

[jivishov]

from sqlalchemy import text

and then try to use
session.execute(text(your_query_string),your_parameters)

it worked for me.

[DrBenjamin]

Hi Jivishov,

that worked… I am just surprised as I thought that I already tried this many times before.

Whatever, thanks a lot!

Ben