Re: [sqlalchemy] Cannot generate models for sqlite db that uses sequence for PK

[Mike Bayer]

On Thu, Sep 13, 2018 at 1:03 PM, Mark Aquino  wrote:
> Took me a little while to figure out what was going on, but it seems the
> code breaks when you try to use a primary key column that uses a sequence in
> sqlite,
>
> e.g. if my schema is this:
>
>
> create table if not exists generic_sequence (
> id integer primary key autoincrement,
> visible_id integer,
> alias text not null,
> content text not null,
> annotation_json clob not null,
> checksum text not null
> );
>
>
> if I tell the constructor of "Model" to print the column and column type for
> each column in the table (line 83 in codegen.py), you can see what happens
> in the output/stacktrace below

So I'm not the sqlacodegen person (he is usually on IRC, not sure if
he reads this list regularly), but this "sqlite_sequence" table was a
surprise to me, but yes it seems to be generated when you happen to
use the special "autoincrement" keyword within the SQLite model.

The two options here are:

1. don't use that "autoincrement" keyword in your database schema, if
possible or

2. whereever you are telling your program to reflect all the tables in
the database (again I don't know sqlacodegen's API), you need to
exclude the table named "sqlite_sequence" as this seems to be a fixed
table that SQLite is generating.   For example, if you were using
metadata.reflect(), you'd want to pass a callable to "only" as:

metadata.reflect(only=lambda name, m: name != 'sqlite_sequence')

this parameter is described at
http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=metadata%20reflect#sqlalchemy.schema.MetaData.reflect.params.only



>
>
> class Model(object):
> def __init__(self, table):
> super(Model, self).__init__()
> self.table = table
> self.schema = table.schema
> print(table)
>
> # Adapt column types to the most reasonable generic types (ie. VARCHAR ->
> String)
> for column in table.columns:
> print(column)
> print(column.type)
> column.type = self._get_adapted_type(column.type, column.table.bind)
>
>
>
> sqlacodegen sqlite:///sqlite.db
>
>
> generic_sequence.id
> INTEGER
>
>
> generic_sequence.visible_id
> INTEGER
>
> generic_sequence.alias
> TEXT
>
>
> generic_sequence.content
> TEXT
>
>
> generic_sequence.annotation_json
> TEXT
>
>
> generic_sequence.checksum
> TEXT
>
> sqlite_sequence
> sqlite_sequence.name
> Traceback (most recent call last):
>   File "/Users/aquinmx3/venv/gbrs-seq-anno3.7/bin/sqlacodegen", line 11, in
> 
> sys.exit(main())
>   File
> "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlacodegen/main.py",
> line 51, in main
> args.noinflect, args.noclasses)
>   File
> "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlacodegen/codegen.py",
> line 418, in __init__
> model = self.table_model(table)
>   File
> "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlacodegen/codegen.py",
> line 93, in __init__
> print(column.type)
>   File
> "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlalchemy/sql/type_api.py",
> line 589, in __str__
> return str(self.compile())
>   File
> "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlalchemy/sql/type_api.py",
> line 573, in compile
> return dialect.type_compiler.process(self)
>   File
> "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py",
> line 293, in process
> return type_._compiler_dispatch(self, **kw)
>   File
> "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py",
> line 81, in _compiler_dispatch
> return meth(self, **kw)
>   File
> "/Users/aquinmx3/venv/gbrs-seq-anno3.7/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py",
> line 2969, in visit_null
> "type on this Column?" % type_)
> sqlalchemy.exc.CompileError: Can't generate DDL for NullType(); did you
> forget to specify a type on this Column?
>
>
> So it looks like a sqlite_sequence object gets implicitly added to the table
> (when you use autoincrement with primary key in sqlite dialect) and it is
> passed to the Model but the code doesn't know how to handle it / what it is.
>
> I assume since the model doesn't need to know about the sequence in sqlite
> (the database will handle the pk generation it doesn't affect the models in
> any way), that just checking for if column == sqlite_sequence then pass
> would fix this but that's just a guess
>
> --
> 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 

[sqlalchemy] Controlling table dependency for flushing

[Alex Rothberg]

Is it possible to hint at sqla the order in which it should write out 
changes to the DB?

I am having issues in which I add two new objects to a session, a and b 
where a depends on b, but sqla is flushing a before b leading to an fk 
issue. I can solve this a few ways: explicitly calling flush after adding 
b, or changing the fk constraint to be initially deferred. Ideally I would 
not have to do either of these.

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

Re: [sqlalchemy] Re: Hybrid Property vs Hybrid Method Expression Names

[Mike Bayer]

On Thu, Sep 13, 2018 at 7:55 AM,   wrote:
> Update: I have just found
> http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#hybrid-attributes-support-reuse-among-subclasses-redefinition-of-getter
> which documents that getters and setters must have the same name as the
> original expression.
>
> Can I just check that it is expected for expressions to have this
> requirement? If so, is it worth opening a docs issue to add this to the main
> hybrid property docs?

this is the mechanics of Python, when you say:

@mything.foobar
def _myotherthing(...)


you are assigning to the name "_myotherthing".   Since
@hybrid_property now creates a copy when any modifier is called, the
original hybrid you have at "mything" was not changed.

All the documentation examples at
http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html
indicate using the same method name for each modification.I can
add more dragons to the hybrid docs as well clarifying that this
naming scheme is intentional and required, if that helps.



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

[sqlalchemy] Re: Hybrid Property vs Hybrid Method Expression Names

[james]

Update: I have just found 
http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#hybrid-attributes-support-reuse-among-subclasses-redefinition-of-getter
 
which documents that getters and setters must have the same name as the 
original expression.

Can I just check that it is expected for expressions to have this 
requirement? If so, is it worth opening a docs issue to add this to the 
main hybrid property docs?

-- 
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] Hybrid Property vs Hybrid Method Expression Names

[james]

Hi,

I've been adding a hybrid property to a model as per the documentation at 
http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html. In 
particular, the behavior is complicated enough that I need to define the 
expression version of the property separate from the normal version.

My model looks like this:

class MyModel(object):
# ...

@hybrid_property
def my_property(self):
# Do some stuff at the instance level

@my_property.expression
def my_property(cls):
# Do some stuff at the class level


This works as expected, but if I change the name of the class function it 
suddenly stops working - based on the error it seems like the renamed class 
function is being ignored and the instance function is being used instead. 
Weirdly, this does not happen when using hybrid methods rather than 
properties - so the following works as expected:

class MyModel(object):
# ...

@hybrid_method
def my_property(self):
# Do some stuff at the instance level

@my_property.expression
def my_property_expression(cls):
# Do some stuff at the class level

This is confusing - from the look of the code, the two decorators seem to 
work in the same way, so I am not sure why the renaming matters to the 
property and not the method. Is this expected behaviour?

Thanks,

James




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

Re: [sqlalchemy] Delete, bulk delete, and Sessions—some clarification needed.

[Simon King]

On Thu, Sep 13, 2018 at 10:45 AM  wrote:
>
> Hello,
>
> I’d like to get some clarification on the following two code examples. First, 
> bulk delete an object:
>
> >>> u = dbsession.query(User).first()
> >>> u.id
> '0078ccdf7db046179c59bff01199c25e'
> >>> dbsession.query(User).filter(User.id == 
> >>> "0078ccdf7db046179c59bff01199c25e").delete()
> >>> dbsession.deleted
> IdentitySet([])
>
> Note how the `deleted` set of the Session object is empty; the objects, 
> however, aren’t yet deleted but will when the Session commits. The other way 
> of deleting would be to delete the objects from the session:
>
> >>> dbsession.delete(u)
> >>> dbsession.deleted
> IdentitySet([ 0078ccdf7db046179c59bff01199c25e>, …])
>
> There is a warning in the documentation of “bulk delete” which says that 
> “[it] is a “bulk” operation, which bypasses ORM unit-of-work automation in 
> favor of greater performance”. I think this refers to the above observation 
> regarding the Session’s `deleted` set, but I don’t know what “unit-of-work 
> automation” refers to.
>
> Can somebody please shed some light on the above observation?
>
> Also, is there a way to detect objects in a Session that are queued to be 
> bulk-deleted? (This would a continuation of a previous discussion “Confusion 
> over session.dirty, query, and flush”.)
>

Query.delete() doesn't "queue objects for deletion". It deletes them
immediately from the database (within the context of the current
transaction).

The default behaviour of Query.delete() is to immediately execute a
"DELETE FROM  WHERE " statement in the database. The
criteria can be as complicated as you like, including calls to SQL
functions and so on. SQLAlchemy has no way of knowing which objects
that are currently loaded in the session would match those criteria,
and by default it doesn't even try. This means that the state of
SQLAlchemy's session no longer corresponds with the state of the
database. When you edit objects in the session, and then call
session.flush(), SA will emit an UPDATE statement. If the
corresponding row has already been deleted via a bulk delete, the
UPDATE statement will fail.

(The unit-of-work is the underlying algorithm that the session uses to
keep track of changes and then execute them against the database when
you call session.flush())

The Query.delete() method takes an optional "synchronize_session"
parameter, which can either be False (the default), "fetch" or
"evaluate". If you pass "fetch", then SA will first issue a "SELECT
 FROM  WHERE " to find out which rows are
about to be deleted. It will remove those from the session, so that
the session once again matches the database.

If you pass "evaluate", SA will try to evaluate the criteria in
Python, to see which objects in the session are about to be deleted.
This is impossible to do in the general case, but it may work for
simple criteria. It will raise an error if the criteria aren't
supported by the evaluator, but note the warning in the docs that "the
expression evaluator currently doesn’t account for differing string
collations between the database and Python".

Hope that helps,

Simon

-- 
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] Delete, bulk delete, and Sessions—some clarification needed.

[jens . troeger]

Hello,

I’d like to get some clarification on the following two code examples. 
First, bulk delete 

 
an object:

>>> u = dbsession.query(User).first()
>>> u.id
'0078ccdf7db046179c59bff01199c25e'
>>> dbsession.query(User).filter(User.id == 
"0078ccdf7db046179c59bff01199c25e").delete()
>>> dbsession.deleted
IdentitySet([])

Note how the `deleted` set of the Session object is empty; the objects, 
however, aren’t yet deleted but will when the Session commits. The other 
way of deleting would be to delete the objects from the session 

:

>>> dbsession.delete(u)
>>> dbsession.deleted
IdentitySet([, …])

There is a warning in the documentation of “bulk delete” which says that 
“[it] is a “bulk” operation, which bypasses ORM unit-of-work automation in 
favor of greater performance”. I think this refers to the above observation 
regarding the Session’s `deleted` set, but I don’t know what “unit-of-work 
automation” refers to.

Can somebody please shed some light on the above observation?

Also, is there a way to detect objects in a Session that are queued to be 
bulk-deleted? (This would a continuation of a previous discussion “Confusion 
over session.dirty, query, and flush” 
.)

Thanks!
Jens

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