0

I wanted to use the CS50 library's SQL function for interacting with a PostgreSQL database.

I have installed PostgreSQL on my computer and removed the need for passwords by modifying the .../Program Files/PostgreSQL/12/data/pg_hba.conf file with the following values:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

I have made a database for testing called suppliers. You can see the database structure and my login credentials in the following image:

enter image description here

When I run the following Python program:

from cs50 import SQL

db = SQL('postgres://postgres:@localhost:5432/suppliers')

I get the following error output:

Traceback (most recent call last):
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\cs50\sql.py", line 276, in execute
    assert flask.current_app
AssertionError

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 782, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 532, in checkout
    rec = pool._do_get()
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 1186, in _do_get
    self._dec_overflow()
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise
    raise value
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 1183, in _do_get
    return self._create_connection()
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 679, in __connect
    pool.dispatch.connect(self.connection, self)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\event\attr.py", line 256, in __call__
    fn(*args, **kw)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\cs50\sql.py", line 67, in connect
    dbapi_connection.isolation_level = None
psycopg2.ProgrammingError: set_session cannot be used inside a transaction

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Huzaifa\Documents\Code\Python\SQLite\0.py", line 3, in <module>
    db = SQL('postgres://postgres:@localhost:5432/suppliers')
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\cs50\sql.py", line 85, in __init__
    self.execute("SELECT 1")
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\cs50\sql.py", line 21, in decorator
    return f(*args, **kwargs)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\cs50\sql.py", line 301, in execute
    self._connection = self._engine.connect()
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2102, in connect
    return self._connection_cls(self, **kwargs)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 90, in __init__
    if connection is not None else engine.raw_connection()
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2188, in raw_connection
    self.pool.unique_connection, _connection)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2162, in _wrap_pool_connect
    e, dialect, self)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1476, in _handle_dbapi_exception_noconnection
    exc_info
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 782, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 532, in checkout
    rec = pool._do_get()
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 1186, in _do_get
    self._dec_overflow()
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise
    raise value
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 1183, in _do_get
    return self._create_connection()
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\pool.py", line 679, in __connect
    pool.dispatch.connect(self.connection, self)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\sqlalchemy\event\attr.py", line 256, in __call__
    fn(*args, **kw)
  File "C:\Users\Huzaifa\Anaconda3\lib\site-packages\cs50\sql.py", line 67, in connect
    dbapi_connection.isolation_level = None
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) set_session cannot be used inside a transaction (Background on this error at: http://sqlalche.me/e/f405)

Please tell me how to get it to work. If it's too hard, please let me know. I'm okay with using a SQLite database if it's too hard to get a PostgreSQL database to work.

By the way, I don't want to use psycopg2. I tried it, but since it returns a list of tuples, with each tuple representing fields in a row (without the names of columns for those fields), when executing SELECT queries, I didn't like it as much as the CS50 library's db.execute() which returns a list of dictionaries with names of columns as keys and fields as values.

1
0

You are using the wrong method of connection. CS50's site mentions that to join with PostgreSQL you should use db = cs50.SQL("postgres://username:password@host:port/database")

Hope this helps. If it does then please check the tickmark.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .