ModuleNotFoundError: No module named 'psycopg2' by using Pyodide

Question

I have only added this code to the *ts file of this repository(https://github.com/ibdafna/webdash) which works fine in my conda environment.

from sqlalchemy.engine.create import create_engine
import pandas as pd
from datetime import datetime

url = 'postgresql://mmnikx ....'
engine =create_engine(url)
now = datetime.now()
df=pd.DataFrame([now])

df.to_sql(
    "TestDBTime",  # table name
    con=engine,
    if_exists='append',
    index=False
)

I am trying to connect to a database using sqlalchemy. This piece of code is inside a typescript file. Running dash with pyodide inside a html file. I think the problem is that I can't load psycopg2 because it is not a pure python package and the pyodide version of sqlalchemy does not have this package.

How could I avoid this problem? My main problem is getting information. Connecting to a database seems to be the best option. I tried to install pygsheets, but no success.

Uncaught (in promise) PythonError: Traceback (most recent call last):
  File "/lib/python3.9/site-packages/_pyodide/_base.py", line 415, in eval_code
    CodeRunner(
  File "/lib/python3.9/site-packages/_pyodide/_base.py", line 296, in run
    coroutine = eval(self.code, globals, locals)
  File "<exec>", line 19, in <module>
  File "<string>", line 2, in create_engine
  File "/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 309, in warned
    return fn(*args, **kwargs)
  File "/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 560, in create_engine
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 782, in dbapi
    import psycopg2
ModuleNotFoundError: No module named 'psycopg2'
    at new_error (pyodide.asm.js:14:246344)
    at pyodide.asm.wasm:0xe6e27
    at pyodide.asm.wasm:0xe6f25
    at Object.Module._pythonexc2js (pyodide.asm.js:14:943337)
    at Object.Module.callPyObjectKwargs (pyproxy.gen.ts:409:12)
    at Object.Module.callPyObject (pyproxy.gen.ts:415:17)
    at PyProxyClass.apply (pyproxy.gen.ts:1191:19)
    at Object.apply (pyproxy.gen.ts:1064:18)
    at Object.runPython (api.ts:57:25)
    at r (worker.js:62:35)

Answer 1

SQLAlchemy requires a driver library to make connections to a particular database.

It is possible to specify which library to use in in connection string, so you don't necessarily need to use psycopg2, the default driver for PostgreSQL.

You could have postgresql+asyncpg://user:pass@host:port/db to use asyncpg, for example.

This may not be a good alternative (unless you want async code). A full list of database driver libraries for PostgreSQL supported by SQLAlchemy is available here. Perhaps one of them will work?

Alternatively, you could try installing the psycopg2-binary package instead, which I've had frequent recourse to, given how fiddly later versions of psycopg2 can be! Note that it is not officially recommended for production, however.

Finally, it sounds like you don't really care if the data are stored in a postgres DB? If the data are lightweight enough that you looked into a spreadsheet as an option, why not consider a SQLite database? Then the database is simply a file that your application has access to. You get sqlalchemy support out of the box and switching is as simple as changing the connection string to sqlite:///path/to/my.db (where my.db is the SQLite database file that you will create).

I created a stripped down example here, if that option interests you - it's very similar to what I think you are trying to do.

Answer 2

Pyodide, or anything running in a web browser, does not support TCP/IP connections. You need a TCP/IP connection to connect to the remote PostgreSQL database. psycopg2, or any other normal SQL database driver cannot work with Pyodide.

Web browsers cannot do TCP/IP connections as it would be a security nightmare as they could connect to any application in your local network.