SQLite 3.35 supports returning #6195
Comments
|
it's brand new as of three weeks ago in https://www.sqlite.org/releaselog/3_35_0.html. pysqlite driver might not even support it, have to try it. |
|
I had already opened an issue here #6038. Will close the old one. SQLite 3.35 added returning and some other useful stuff, like column removal that may be useful to support in alembic. See for a list of features: https://nalgeon.github.io/sqlite-3-35/ |
That will likely take some time unless we want to build everything from source. Python 3.9 ships with SQLite 3.31.1. At the moment, Python 3.10a6 includes that same version. |
|
@CaselIT oh whoops! @gordthompson I have sqlite building from source in our jenkins CI in any case to support some other sqlite features too : https://github.com/sqlalchemyorg/ci_containers/blob/master/roles/jenkins/files/install_sqlite.sh |
|
Conda (on windows but I would guess also on linux) already ships with sqlite 3.35.4 for python 3.9.2.
|
zzzeek
added
sql
use case
|
Windows Python 3.9.5 ships with 3.35.5 |
|
On the github action we have:
Also tried in the docker python images and the debian based are still on 3.27, while alpine is on 3.34 |
|
In our own CI my images build the latest SQLite from source before compiling python so I can have these on jenkins after doing a rebuild. |
|
@zzzeek Do we know approximately the release date of 2.0? |
|
there is no current eta yet |
|
I tried to improvise "returning" in the following way: import asyncio
from typing import Optional
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker, declarative_base, DeclarativeMeta
from sqlalchemy import select, text, Column, Integer, String, Index, or_, func
from sqlalchemy.dialects.sqlite import insert
from pydantic import BaseModel
import pandas as pd
import sys
Base: DeclarativeMeta = declarative_base()
db_path = "sqlite+aiosqlite:///database.sqlite"
engine = create_async_engine(db_path, echo=True, future=True)
async_session_maker = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
print(f"{sys.version=}")
class NewTest(Base):
__tablename__ = 'new_test'
id = Column(Integer, primary_key=True)
fld_text = Column(String)
fld_int1 = Column(Integer, nullable=True)
fld_int2 = Column(Integer, nullable=True)
Index('new_test_idx', NewTest.fld_text, unique=True)
class NewTestInDb(BaseModel):
id: int
fld_text: str
fld_int1: Optional[int]
fld_int2: Optional[int]
class Config:
orm_mode = True
old_records = [
NewTestInDb(id=1, fld_text='A', fld_int1=1, fld_int2=1),
NewTestInDb(id=2, fld_text='B', fld_int1=2, fld_int2=2),
NewTestInDb(id=3, fld_text='C', fld_int1=3, fld_int2=3),
NewTestInDb(id=4, fld_text='D', fld_int1=4, fld_int2=4),
NewTestInDb(id=5, fld_text='E', fld_int1=None, fld_int2=None),
NewTestInDb(id=6, fld_text='F', fld_int1=None, fld_int2=None),
NewTestInDb(id=7, fld_text='G', fld_int1=None, fld_int2=None),
NewTestInDb(id=8, fld_text='H', fld_int1=None, fld_int2=None),
]
updated_records = [
NewTestInDb(id=1, fld_text='A', fld_int1=1, fld_int2=1),
NewTestInDb(id=2, fld_text='B', fld_int1=None, fld_int2=20),
NewTestInDb(id=3, fld_text='C', fld_int1=30, fld_int2=None),
NewTestInDb(id=4, fld_text='D', fld_int1=40, fld_int2=40),
NewTestInDb(id=5, fld_text='E', fld_int1=None, fld_int2=None),
NewTestInDb(id=6, fld_text='F', fld_int1=6, fld_int2=None),
NewTestInDb(id=7, fld_text='G', fld_int1=None, fld_int2=7),
NewTestInDb(id=8, fld_text='H', fld_int1=8, fld_int2=8),
]
async def main():
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.drop_all)
await conn.run_sync(Base.metadata.create_all)
async with async_session_maker() as session:
async with session.begin():
print('----------------- beginning state --------------------')
await session.execute(insert(NewTest).values([val.dict() for val in old_records]))
res = (await session.execute(select(NewTest))).scalars().all()
df = pd.DataFrame([NewTestInDb.from_orm(row).dict() for row in res])
print(df)
# inserting/updating rows inserted above
ins_stmt = insert(NewTest).values([val.dict() for val in updated_records])
ins_stmt = ins_stmt.on_conflict_do_update(
index_elements=('fld_text',),
index_where=or_(
NewTest.fld_int1 != func.ifnull(ins_stmt.excluded.fld_int1, NewTest.fld_int1),
NewTest.fld_int2 != func.ifnull(ins_stmt.excluded.fld_int2, NewTest.fld_int2)
),
set_=dict(
fld_int1=func.ifnull(ins_stmt.excluded.fld_int1, NewTest.fld_int1),
fld_int2=func.ifnull(ins_stmt.excluded.fld_int2, NewTest.fld_int2)
)
)
stmt = str(ins_stmt.compile(compile_kwargs={"literal_binds": True})) + ' returning *;'
res_final = (await session.execute(text(stmt))).all()
df = pd.DataFrame([row for row in res_final])
print(df)
loop = asyncio.get_event_loop()
loop.run_until_complete(main())Output: The idea here is to be able to insert the same records or records with updated fields to the table, but get back only rows that have been inserted/updated. Update only if new value is not null. |
|
Above workaround by @ghuname looks promising and works for me because my tables include import sqlite3
import traceback
from sqlalchemy import create_engine, text
ver = sqlite3.sqlite_version.split('.')
major = int(ver[0])
minor = int(ver[1])
assert major > 3 or major == 3 and minor >= 35
with sqlite3.connect(':memory:') as conn:
print('########## sqlite3 test ##########')
conn.execute('CREATE TABLE test(id INTEGER PRIMARY KEY, s TEXT)')
cursor = conn.execute(
"INSERT INTO test (s) VALUES ('one'), ('two') RETURNING *"
)
rows = list(cursor)
assert len(rows) == 2
assert rows[0][0] == 1 and rows[0][1] == 'one'
assert rows[1][0] == 2 and rows[1][1] == 'two'
print('OK')
engine = create_engine('sqlite:///:memory:', echo=True)
with engine.connect() as conn:
print('\n############ sqlalchemy test ############')
conn.execute('CREATE TABLE test(id INTEGER PRIMARY KEY, s TEXT)')
try: # ERROR (missing .execution_options(autocommit=False)
print('\n############ INSERT ... RETURNING - ERROR ############')
result = conn.execute(
text("INSERT INTO test (s) VALUES ('one'), ('two') RETURNING *")
)
except Exception:
traceback.print_exc()
print('\n############ INSERT ... RETURNING ############')
cursor = conn.execute(
text(
"INSERT INTO test (s) VALUES ('one'), ('two') RETURNING *"
).execution_options(autocommit=False)
)
rows = list(cursor)
assert len(rows) == 2
assert rows[0].id == 1 and rows[0].s == 'one'
assert rows[1].id == 2 and rows[1].s == 'two'
try:
conn.execute('COMMIT')
assert False
except Exception:
pass # COMMIT happened despite autocommit=False
with conn.begin():
print('\n############ INSERT ... RETURNING inside transaction ############')
cursor = conn.execute(
text(
"INSERT INTO test (s) VALUES ('three'), ('four') RETURNING *"
)
)
rows = list(cursor)
assert len(rows) == 2
assert rows[0].id == 3 and rows[0].s == 'three'
assert rows[1].id == 4 and rows[1].s == 'four'Output: |
|
A note on adding RETURNING support for the SQLite dialect: I don't know what its current state is in 2.0, but as a quick hack I was able to apply a patch directly in - "restrict",
- "right",
+ "restrict",
+ "returning", # Adds RETURNING reserved keyword.
+ "right",-class SQLiteCompiler(compiler.SQLCompiler):
+class SQLiteCompiler(compiler.SQLCompiler):
+ def returning_clause(self, stmt, returning_cols):
+ """Adds compile logic for RETURNING clause."""
+
+ columns = [
+ self._label_returning_column(stmt, c)
+ for c in sql.expression._select_iterables(returning_cols)
+ ]
+ return "RETURNING " + ", ".join(columns)- construct_arguments = [
+ implicit_returning = True # Enables RETURNING syntax.
+ full_returning = True # Enables RETURNING syntax.
+
+ construct_arguments = [And although due to the following SQLite limitation,
you can't embed it in a CTE via |
|
If you can submit a pr with the changes and the tests it would be great! |
|
I havent yet looked to see what kinds of curveballs this one might have. we'll be getting to this in the coming weeks regardless. |
|
Daniel Black has proposed a fix for this issue in the main branch: Generalize RETURNING and suppor for MariaDB / SQLite https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/3889 |
|
Is this fix waiting for 2.0 release? Would it be possible to get it into a 1.4.x? |
yes
no, the patch that generalized RETURNING was for sqlite and mariadb and was fairly enormous (466ed5b) when test support is taken into account and also had to make some very esoteric changes to the way INSERT statements are rendered for dialects that already support cursor.lastrowid. Adding a feature to 1.4.x means I have to support it when it breaks, and as we are at 1.4.41 very deep into the 1.4 series, this is not the time for major new behaviors / support cases to be added. That said, to get SQLite to render an explicit RETURNING clause, you could likely just add the part of the code right here: sqlalchemy/lib/sqlalchemy/dialects/sqlite/base.py Lines 1323 to 1334 in 466ed5b that won't integrate RETURNING into any ORM processes or anything like that, however. |
|
Thanks for the answer and explanation.
I will wait patiently for the release!
Is there any plan or estimate on when 2.0 (or maybe a beta) will be released?
…On Thu, Sep 15, 2022 at 03:29:14PM -0700, mike bayer wrote:
> Is this fix waiting for 2.0 release?
yes
>
> Would it be possible to get it into a 1.4.x?
no, the patch that generalized RETURNING was for sqlite and mariadb and was fairly enormous (466ed5b) when test support is taken into account and also had to make some very esoteric changes to the way INSERT statements are rendered for dialects that already support cursor.lastrowid. Adding a feature to 1.4.x means I have to support it when it breaks, and as we are at 1.4.41 very deep into the 1.4 series, this is not the time for major new behaviors / support cases to be added.
That said, to get SQLite to render an explicit RETURNING clause, you could likely just add the part of the code right here: https://github.com/sqlalchemy/sqlalchemy/blob/466ed5b53a3af83f337c93be95715e4b3ab1255e/lib/sqlalchemy/dialects/sqlite/base.py#L1323-L1334
that won't integrate RETURNING into any ORM processes or anything like that, however.
--
Reply to this email directly or view it on GitHub:
#6195 (comment)
You are receiving this because you are subscribed to this thread.
Message ID: ***@***.***>
|
|
the first betas should came out in the fall |
this is pretty major functionality so we should try to support it, somewhat like how we support mariadb returning, i.e. where it's not supported on many variants but works when called upon if the backend supports it.
The text was updated successfully, but these errors were encountered: