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:
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.
