sqlalchemy: commit after insert

[jerrinot]

questdb-connect uses psycopg2 under the hood and transaction must be committed explicitly. without this change the SELECT does not see previous INSERT.

[github-actions[bot]]

:rocket: Build success!

Latest successful preview: https://66f3d07cde5df2030cfc672b--unruffled-blackwell-31bfb2.netlify.app

Commit SHA: 461836a143958823d32e62fcbcdb1cdf165ce324

:package: Build generates a preview & updates link on each commit.

[ideoma]

Wha SQL Alchemy version you tested the example with?

[jerrinot]

2.0.35

[jerrinot]

[jerrinot]

this is the output without commit()

[('t',), ('market_data',), ('x',), ('some_table',)]
[]
[]
Table('some_table', MetaData(), Column('x', Int(), table=<some_table>), Column('y', Int(), table=<some_table>), schema=None)
Traceback (most recent call last):
  File "/Users/jara/devel/oss/questrun_sqlalchemy/runner_sqlalchemy.py", line 38, in <module>
    conn.execute(text("DROP TABLE some_table"))
  File "/Users/jara/devel/oss/questrun_sqlalchemy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
  File "/Users/jara/devel/oss/questrun_sqlalchemy/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/jara/devel/oss/questrun_sqlalchemy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/jara/devel/oss/questrun_sqlalchemy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
  File "/Users/jara/devel/oss/questrun_sqlalchemy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/Users/jara/devel/oss/questrun_sqlalchemy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2358, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/Users/jara/devel/oss/questrun_sqlalchemy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/Users/jara/devel/oss/questrun_sqlalchemy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/jara/devel/oss/questrun_sqlalchemy/.venv/lib/python3.9/site-packages/questdb_connect/__init__.py", line 61, in execute
    return super().execute(remove_public_schema(query), vars)
psycopg2.DatabaseError: could not lock 'some_table' [reason='insert']
LINE 1: DROP TABLE some_table
        ^

and with conn.commit() after insert:

[('t',), ('market_data',), ('x',), ('some_table',)]
[(11, 12), (13, 14)]
[(11, 12), (13, 14)]
Table('some_table', MetaData(), Column('x', Int(), table=<some_table>), Column('y', Int(), table=<some_table>), schema=None)

Process finished with exit code 0

[ideoma]

We want it to work with both 2 and 1.4 versions, can you please test with 1.4 as well?

[ideoma]

Also, can you please change section

Prerequisites
Python from 3.9 to 3.11
Psycopg2
SQLAlchemy <= 1.4.47

to state that we support 1.4 and 2.0 version?

[jerrinot]

@ideoma good call with testing 1.4!

it turns out the connection object in sqlalchemy 1.4 does not have commit() and it's running in auto-commit mode. so either we figured out how to change the commit mode or we'll need 2 examples.

[jerrinot]

this works with both 1.4 and 2.0:

from sqlalchemy import create_engine
from sqlalchemy import text
from sqlalchemy import MetaData
from sqlalchemy import Table
from pprint import pprint

engine = create_engine("questdb://admin:quest@localhost:5432/qdb")

with engine.begin() as conn:
  # SQL statements with no parameters
  conn.execute(text("CREATE TABLE IF NOT EXISTS some_table (x int, y int)"))
  result = conn.execute(text("SHOW TABLES"))
  print(result.all())
  # results can be iterated in many ways. Check SQLAlchemy docs for details

with engine.begin() as conn:
  # passing parameters to your statements
  conn.execute(
    text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
    [{"x": 11, "y": 12}, {"x": 13, "y": 14}],
  )

with engine.begin() as conn:
  # basic select, no parameters
  result = conn.execute(text("select * from some_table"))
  print(result.all())

  # select with parameters
  result = conn.execute(text("SELECT x, y FROM some_table WHERE y > :y"), {"y": 2})
  print(result.all())

  # partial support for metadata
  metadata_obj = MetaData()
  some_table = Table("some_table", metadata_obj, autoload_with=engine)
  pprint(some_table)

  # cleaning up
  conn.execute(text("DROP TABLE some_table"))

IT's based on the migration guide as documented at https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#migration-core-connection-transaction

It makes the example more complicated. WDYT?

[jerrinot]

The guide mentions that "driver-level" auto-commit can be achieved by setting ISOLATION_LEVEL(🤯 ) to AUTOCOMMIT. I tried that, but this does not work. Chances are the QuestDB driver ignores isolation levels and does not handle auto-commit.

[jerrinot]

closing in favour of https://github.com/questdb/questdb.io/pull/2449