psycopg2 support #2143
Description
Hi,
I'm wondering if doltgresql supports pyscopg2 well (and if not, what library should I use for programmatic access with python)?
When I try to query a Doltgresql using psycopg2, all cursor fetch methods (fetchall(), fetchone(), fetchmany()) return significantly fewer rows than SELECT COUNT(*) indicates exist in the table.
Exact steps I tried:
-
Create a database and table with a SERIAL PRIMARY KEY:
CREATE TABLE pk_test_table ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, value INTEGER NOT NULL );
-
Insert 1000 rows:
for i in range(1000): cur.execute("INSERT INTO pk_test_table (name, value) VALUES (%s, %s)", (f"item_{i}", i * 10)) conn.commit()
-
Verify count:
cur.execute("SELECT COUNT(*) FROM pk_test_table;") count = cur.fetchone()[0] # This returns 1000
-
Attempt to fetch all primary keys:
cur.execute("SELECT id FROM pk_test_table;") all_rows = cur.fetchall() # This only return 104 rows
The missing IDs appear to be the first 896 rows (IDs 1-896). The returned 104 rows are likely the last rows inserted (IDs 897-1000).
I'm wondering if I did anything wrong above, or if this is expected behavior and I shouldn't be interacting with doltgresql with psycopg2?
Any help/suggestions would be appreciated!