On Sat, Jan 6, 2018 at 3:31 PM, Florian Apolloner <f.apollo...@gmail.com> wrote:
>
>
> On Saturday, January 6, 2018 at 8:53:41 PM UTC+1, Mike Bayer wrote:
>>
>> Can you confirm the exact sql and parameters you are seeing? SQLAlchemy
>> never sends NULL for an auto increment id column, it omits it from the
>> statement so that the default takes place, which I assume is what you mean
>> by "leave it out".
>
>
> According to --log-info=sqlalchemy.engine:
>
> ```
> test/test_suite.py::DateTest_informix+ibmdb::test_null <-
> ../../sources/sqlalchemy/lib/sqlalchemy/testing/suite/test_types.py
> INFO:sqlalchemy.engine.base.Engine:INSERT INTO date_table (id, date_data)
> VALUES (?, ?)
> INFO:sqlalchemy.engine.base.Engine:(None, None)
> ```
>
> I wouldn't be surprised if this is an issue in my dialect; but I didn't
> really figure out where yet. Maybe something is off with those options:
> https://gitlab.com/apollo13/sqlalchemy-informix/blob/master/sqlalchemy_informix/ibmdb.py#L262-265
so yes it is those options, since there is no method given for the
dialect to either pre-fetch or post-fetch the defaulted primary key
that the database generates. It's assuming your dialect will run the
sequence explicitly ahead of time and make that part of the INSERT
statement.
When you run an INSERT and the default fires off, you need a way of
accessing the newly generated integer index after the statement is
executed, and if that's not possible, you need to provide the new
integer primary key explicitly as generated by the sequence. The
current varieties are:
1. SELECT nextval(seq) on the sequence, then provide the value as
explicit to the INSERT. Postgresql dialect does this for old PG
versions that don't support RETURNING, the Oracle dialect used to work
this way also
print stmt.compile(dialect=postgresql.dialect(implicit_returning=False),
column_keys=[])
INSERT INTO a (id) VALUES (%(id)s)
2. Run the INSERT statement with RETURNING, assuming the sequence is
implicit, to get the newly generated PK - modern PG dialect does this,
SQL server dialect also:
print stmt.compile(dialect=postgresql.dialect(implicit_returning=True),
column_keys=[])
INSERT INTO a DEFAULT VALUES RETURNING a.id
3. Run the INSERT statement with RETURNING, where the sequence has to
be explicit, Oracle dialect does this:
print stmt.compile(dialect=oracle.dialect(implicit_returning=True),
column_keys=[])
INSERT INTO a (id) VALUES (id_seq.nextval) RETURNING a.id INTO :ret_0
4. The driver provides a cursor.lastrowid function, part of the DBAPI;
this lastrowid is usually selecting something like LAST_INSERT_ID(),
SQLite and MySQL drivers do this:
print stmt.compile(dialect=sqlite.dialect(), column_keys=[])
INSERT INTO a DEFAULT VALUES
5. we will post fetch the identifier but we need to emit special
functions that aren't part of cursor.lastrowid - SQL Server dialect
does this when RETURNING isn't used
So you need to know:
1. will you always use sequences?
2. are the sequences part of a database-side implicit system like PG
SERIAL or SQL Server IDENTITY or do they need to be explicitly
rendered like on Oracle?
3. does your database support RETURNING?
4. does your database support some kind of "fetch last id" function
that you run after an INSERT statement?
5. does your DBAPI provide #4 as cursor.lastrowid or not?
then we will know what your flags should be set towards.
> ?
>
> Thanks & cheers,
> Florian
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
