[sqlalchemy] compare a object/instance against its data in the database

Is there a way to find out if a persistent sqlalchemy mapped object was modified? It means I recieve a object (persistent, with identiy) from the database. Then the user (maybe!) modify its data in a dialog-window. obj = session.query(MyClass).first() LetTheUserDoSomethingWithIt(obj) if obj

[sqlalchemy] visualize structure of the data

I was looking for a way to create a visualization (ER-Diagram, UML, everthing visual) of my data structure in the givin SQL-CREATE TABLE statements. But I couldn't find some. So now I had the idea that there is maybe a solution to create something like this out of the SQLA-classes I have for the t

[sqlalchemy] Use order_by when using relationship() with secondary-parameter in a many-to-many?

I create a many-to-many relationship between a `Person` (as an _author_) and a `Reference` (as a _scientific publication_). ReferenceAuthor = sa.Table('ReferenceAuthor', _Base.metadata, sa.Column('ReferenceID', sa.Integer, sa.ForeignKey('Reference.ID'), primary_key=True), sa.Column('Perso

Re: [sqlalchemy] Use order_by when using relationship() with secondary-parameter in a many-to-many?

Hi Mike On 2016-02-13 14:30 Mike Bayer wrote: > you can order by secondary, just add it in: > > authors = relationship("Person", secondary=ReferenceAuthor, > order_by=ReferenceAuthor.c.Index) Are you sure? I get this error about it TypeError: relationship() got an unexpected keyword argument

Re: [sqlalchemy] Use order_by when using relationship() with secondary-parameter in a many-to-many?

> >> order_by=ReferenceAuthor.c.Index) >order_by, you spelled it wrong. Sorry, I can not see a difference. I think I missunderstand you. btw: What does the 'c' means? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this g

Re: [sqlalchemy] Use order_by when using relationship() with secondary-parameter in a many-to-many?

On 2016-02-14 08:38 Michael Bayer wrote: > order_by, you spelled it wrong. Ah, I see the misstake. Was a copy&paste thing. Sorry. What is this c? I havend found that "topic" in the docs. Where should I look for? -- You received this message because you are subscribed to the Google Groups "sql

[sqlalchemy] how to handle SQLite's GUID?

What would be the best and most save way to represent the "GUID PRIMARY KEY" column type from SQLite3 in a SQLAlchemy schema? The SQL looks like this CREATE TABLE Reference (ID GUID CONSTRAINT PK_Reference PRIMARY KEY, ... The DB ist foreign and not created by me. -- You received this message

Re: [sqlalchemy] how to handle SQLite's GUID?

Ok, sorry. I used unconcrete words. I don't want to create something like that. I want to know which one of the standard SQLAlchemy-offered data types would be the best fitting to handle (just read and use for UPDATE) a vendor-column-type like that. -- You received this message because you are

[sqlalchemy] order by child object's field

I want to query specific objects of Reference. But I want that list ordered by its child objects (you could say relation): Reference._periodical._name and `Reference._author._lastname'. Can not see how to do this. Not sure if this is even possible? This is the query but without the ordering in it

Re: [sqlalchemy] Re: order by child object's field

On 2016-03-20 13:12 Jonathan Vanasco wrote: > .contains_eager('_periodical')\ # this lets sqlalchemy This eager-loading thing is a nice hint. I will use this in the future. Thanks. I haven't specified a important part of my question because I didn't think about it. :) A "Reference" c

Re: [sqlalchemy] Re: order by child object's field

> ReferenceAuthor is an instance of sqlalchemy.Table, so you can refer > to its columns as ReferenceAuthor.c.Index. Ah nice. But something is still wrong. Part of the query: .join(ReferenceAuthor, ReferenceAuthor.c.Index=0) \ result in ReferenceAuthor.c.Index=0) \

Re: [sqlalchemy] Re: order by child object's field

Hi Michal > q.join(ReferenceAuthor, ReferenceAuthor.c.Index == 0) Ah, of course! Thank you very much! But... ;) This Python code return self.session.query(Reference) \ .filter_by(_mark = False) \ .join(Periodical) \ .join(Referenc

Re: [sqlalchemy] Re: order by child object's field

Dear Simon, thanks. This works and look great. I should stick this code on my sleeping pillow. :D >From viewpoint of SQL or the database this makes totally sense to me. But my fault (in thinking) was expect that the relationship() definitions I did in the classes would be enough for SQLA to know

[sqlalchemy] How to use make_transient() to duplicate an SQLAlchemy mapped object?

I opened a questions with example (pseudo) code on stackoverflow for that. I know the question how to duplicate or copy a SQLAlchemy mapped object was asked a lot of times. The answer a

Re: [sqlalchemy] How to use make_transient() to duplicate an SQLAlchemy mapped object?

Hi Mike, thanks for your help. On 2015-05-22 02:32 Mike Bayer wrote: > if you copy an object to transient, now instance_state.key is gone, > next step is erase the primary key column-holding attributes, such as > myobject.id = None. object on flush will have no PK value and > autoincrement w

[sqlalchemy] don't lose the relationship() reference when calling make_transient()

It cost me some time to analyse this problem. ;) The problem in the pseudo-code below is that 'obj' lose the reference to its foreign-key object (set with relationship()). The foreign key member itself is still correct set with a numeric value. I understand that this can happen because of the ref

Re: [sqlalchemy] don't lose the relationship() reference when calling make_transient()

On 2015-05-23 18:30 wrote: > The problem in the pseudo-code below is that 'obj' lose the reference > to its foreign-key object (set with relationship()). The foreign key > member itself is still correct set with a numeric value. I tried load_on_pending=True but this doesn't help and I think I mis

Re: [sqlalchemy] don't lose the relationship() reference when calling make_transient()

On 2015-05-23 18:30 wrote: > I understand that this can happen because of the reference cycle > counting stuff in the background of SQLA. But how can I prevent this? Maybe I should get into transaction handling with SQLA to prevent problems like this. ;) -- You received this message because you

[sqlalchemy] Session.close() implicite

What do you think about that (simplified) code? An instance of this class would be added to any gui-class (e.g. a dialog) which handle data over SQLAlchemy. The session would be destroyed/closed correct when the Controller instance is destroyed, too. Correct? [code] import sqlalchemy as sa import

[sqlalchemy] ...and MVC

I know there is no absolute solution for my question. I just want to hear your suggestions and how you do this in your practice. In which layer of the MVC-pattern are all the SQLAlchemy objects located? There should be a Session for each action. But who does this action? The controller? In my cur

Re: [sqlalchemy] Session.close() implicite

On 2015-05-25 11:12 Mike Bayer wrote: > CreateSession and sessionmaker do the same thing. I'd lose one or > the other. Yes, I will do so. > For the memoized thing, I'd use a decorator like Pyramid @reify or > SQLAlchemy's @memoized_property, wouldn't bother with all those > conditionals and s

[sqlalchemy] difference between expunge() and make_transient()

Can someone explain me the difference between expunge() and make_transient() please. Of course I read the docs (more than one time) about it. But I am not really sure about the difference. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubs

Re: [sqlalchemy] difference between expunge() and make_transient()

On 2015-05-26 13:06 Mike Bayer wrote: > expunge sends a "persistent" object to the "detached" state. > make_transient converts a "persistent" object to a "transient". These > states are described at When I expunge() an object (it became "detached") and after that modifying its values (maybe th

[sqlalchemy] subscribe

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

[sqlalchemy] How to find out if an object is a SQLAlchemy mapped one?

How can I find out if an object is a SQLAlchemy mapped one? It means if it is derived from sqlalchemy.ext.declarative.declarative_base(). Using isinstance() doesn't work in my tests. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe f

Re: [sqlalchemy] How to find out if an object is a SQLAlchemy mapped one?

On 2015-06-05 20:53 wrote: > How can I find out if an object is a SQLAlchemy mapped one? [code] #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlalchemy import sqlalchemy.ext.declarative Base = sqlalchemy.ext.declarative.declarative_base() class Model(Base): __tablename__ = 'Table'

Re: [sqlalchemy] How to find out if an object is a SQLAlchemy mapped one?

On 2015-06-05 20:53 wrote: > How can I find out if an object is a SQLAlchemy mapped one? This work, but I am not sure if it is elegant. What do you think? type(type(model)) == type(sqlalchemy.ext.declarative.declarative_base()) -- You received this message because you are subscribed to the Goo

[sqlalchemy] is attribute a Foreign- or PrimaryKey?

How can I find out (based on the given code) if an attribute is a foreign key, a primary key or nothing of that? insp = sqlalchemy.inspection.inspect(model) attrs = insp.attrs for attr in attrs: print('{}="{}"'.format(attr.key, attr.value) The SQLA-docu is still quite hard fo

[sqlalchemy] create/restore a dump-file

I read about the Serializer Extension and ask myself if it is a good solution for my problem. Currently I am using PostgreSQL under my sqlalchemy. There is no way to make PostgreSQL database "portable" because the database is not represented as one file I could just copy to another machine. So wh

[sqlalchemy] convert PostgreSQL to sqlalchemy

Could it be possible to use SQLAlchemy with Python3 to convert a PostgreSQL database to a sqlite one? -- 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+un

[sqlalchemy] convert PostgreSQL to sqlite

Could it be possible to use SQLAlchemy with Python3 to convert a PostgreSQL database to a sqlite one? -- 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+un

Re: [sqlalchemy] convert PostgreSQL to sqlite

I tried this code. There are two objects in it. The second is just a new created one to check if everything goes right. It appears in the sqlite database. The first object is from the "old" PostgreSQL database. But it doesn't appear in the new sqlite database. There even is no error output and now

Re: [sqlalchemy] convert PostgreSQL to sqlite

Forget the old code. I needed to use make_transient() This example code work nearly fine (FOREIGN KEY constraints not used in sqlite) #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlalchemy as sa import sqlalchemy.ext.declarative as sad import sqlalchemy.orm as sao import sqlalchemy.orm.

[sqlalchemy] What means this 'Decimal()'?

X-Post on stackoverflow: I use SQLAlchemy with Python3 and sqlite3. I tried to `add()` and new object into a sqlite database and got this error message. sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 3 - probably

[sqlalchemy] supress SAWarning about decimal numbers

I use SQLAlchemy with sqlite. I got a warning about conversion problems of "Numeric" data to sqlite. I know why and that is no problem for me - it is just a weight value. The message starts with: "sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite" Is there a way to suppress this message? -- Y

Re: [sqlalchemy] supress SAWarning about decimal numbers

On 2015-07-11 01:21 wrote: > The message starts with: > "sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite" > > Is there a way to suppress this message? Helped myself import warnings msg = 'Dialect sqlite\+pysqlite does \*not\* support ' \ 'Decimal objects' warnings.f

[sqlalchemy] How to get a back from a SQLAlchemy query?

I have (sqlite3) database with a `Numeric(10, 2)` field. I want to query for that and work with it as a standard python datatype (for me this is ``). In the code below I want a list of floats - nothing more. How can I do this without manual converting the list? # Python3 pseudocode class M

[sqlalchemy] func.substr() documentation?

I see in a lot of example code "func.substr()". But I can not find it in the official docs (not in my local ones, not on the website). Did I use the search feauter wrong? -- -BEGIN PGP PUBLIC KEY BLOCK- Version: GnuPG v1 mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU G0

[sqlalchemy] Why is there no SQLA-List class?

I know that in most all RDBMS are no implementations of lists or arrays. When I want that I can use VARCHAR or something like that. And I can query for substr() in that field. This is ok on a SQL-level. But what is about SQLAlchemy? I see nowhere a implementation of a List() type that encapsulate

Re: [sqlalchemy] Why is there no SQLA-List class?

On 2015-08-02 19:15 wrote: > But what is about SQLAlchemy? I see nowhere a implementation of a > List() type that encapsulate that VARCHAR-depending converting work? When implementing this myself. I am not sure if I have to use TypeDecorator or UserDefinedType as baseclass for that. I am not sur

Re: [sqlalchemy] Re: func.substr() documentation?

On 2015-08-03 01:17 Pavel S wrote: > You should refer documentation of your RDBMS. It is sqlite3. > '*func*' is just proxy to any function defined inside RDBMS. > > If your RDBMS has function *bla* (e.g. CREATE FUNCTION bla...), then > you can call if from python using *func.bla()*. Ah, nice t

Re: [sqlalchemy] Why is there no SQLA-List class?

I never used JSON before or know something about it. I will check that. On 2015-08-03 14:38 Adam Tauno Williams wrote: > On the contrary - and emphatically - just about every modern RDMBS > supports collection types including lists, sets, multi-sets, and > key-value data. Can you specify tha

Re: [sqlalchemy] Why is there no SQLA-List class?

On 2015-08-02 19:15 wrote: > But what is about SQLAlchemy? I see nowhere a implementation of a > List() type I thinking about if it's easier to just use a conservative Many-to-Many-Solution with an extra table plus a link-table. I am scared about that there are limitations about quering on a JSO

[sqlalchemy] modify the data structure of an existing and filled database

I thinking about how to modify the data structure of an existing and filled (e.g. sqlite3) database. For example class Model(_Base): __tablename__ = 'Model' Column('oid', Integer, primary_key=True) Column('field', String) The String field should be changed to an Integer. How would you do

[sqlalchemy] Alter a column from an Integer to a many-to-many relationship

I want to alter a column in a sqlite database from an Integer to a many-to-many relationship (maybe with alembic). I tried a little bit arround and created an example code for that. But it didn't work. And I am not sure if alembic is the right solution for my case. Please see the details at stack

[sqlalchemy] [alembic] how do I add a many-to-many relation with add_column()

I want to add a new column which is a many-to-many relation to a new created table. I am not sure what is wrong with the code below. #!/usr/bin/env python3 # -*- coding: utf-8 -*- import sqlalchemy as sa import sqlalchemy.orm as sao import sqlalchemy.ext.declarative as sad import sqlalchemy_utils

Re: [sqlalchemy] [alembic] how do I add a many-to-many relation with add_column()

On 2015-08-11 21:43 Mike Bayer wrote: > a relationship() is not a Column. Alembic "add_column()" is > intended to render an "ALTER TABLE ADD COLUMN " > statement. You need to pass it a Column object. Ok, tnen how can I realize that. How can I add a relationship to an existing table? Is alembi

[sqlalchemy] [alembic] import the own model into myproject/alembic/env.py

I want to use `alembic revision --autogenerate` with my own model classes. Because of that I need to import them in `myproject/alembic/env.py` as [described in the docs][1]. But this doesn't work even if I tried a lot of variations. I am not sure in which context (don't know if this is the correct

[sqlalchemy] copy the scheme of a table

I tried a little bit around and checked the docs of course. Is there a way to just "copy" the column-definitions of one table to another? class Model(Base): __tablename__ = 'Model' oid = Column(Integer, primary_key=True) data = Column(String) AnotherModel = Table('AnotherModel', metad

[sqlalchemy] how to find none-referenced objects

Instances of 'A' can have many references to instances of 'B'. I want to find all 'B' instances that are not referenced by an 'A'. I am not sure if this is possible with SQLAlchemy-Query. And I am not sure if the backref() part in the code is needed for that. What do you think? a_b_relation= sa.

Re: [sqlalchemy] how to find none-referenced objects

On 2015-09-20 14:05 wrote: > Instances of 'A' can have many references to instances of 'B'. > I want to find all 'B' instances that are not referenced by an 'A'. I am sorry. Of course I looked into the docs before. But for me it is hard to get into its structure to find what I am looking for. And

[sqlalchemy] Handle multiple relations between two rows/objects

This is related to this post I found this problem in my application using Python3 with a SQLAlchemy connected sqlite-database. Simple description: Entity `a` (from table/class `A`) can h

Re: [sqlalchemy] Handle multiple relations between two rows/objects

Hi Mike, thanks a lot for your help. I know the concepts of db-normalization and 3NF - but haven't worked so much with that in practice. One of my problems is that I think the object-orientated way. I started quite early to work with OODBMS instead of RDBMS. I an OODBMS SetSet would just a list/a

Re: [sqlalchemy] Re: Handle multiple relations between two rows/objects

On 2015-09-30 15:43 Jonathan Vanasco wrote: > Most ORMs, SqlAlchemy included, require a primary key. But simply adding a 'oid' to my relation-table doesn't solve the problem. tu_set_relation = sa.Table('tu_set_relation', _Base.metadata, sa.Column('oid', sa.Integer, primary_key=True), sa.

Re: [sqlalchemy] Handle multiple relations between two rows/objects

Maybe I am a little bit nearer with that? Set_Values: oid, repetitions, level SetSet: oid, set_val_oid TrainingUnit: oid, ... tu_s_rel: trainingunit_oid, setset_oid What do you think? Wouldn't that be a little bit to much overhead only to prevent redudance of 'repetitions' and 'level'? -- -

[sqlalchemy] [listener] before update a column

I want to know (by listeners) when a specific field/column of a row will be updated before it is done! I need to know the current/old value and the new (after update) value. e.g. CREATE TABLE "SetSet" ( oid INTEGER NOT NULL, set_value_fk INTEGER NOT NULL, PRIMARY KEY (oid

[sqlalchemy] store a image(file)

Could you please turn me in the right direction. ;) I want to have a image/picture (png) of a person stored in a Person-table. class Person(_Base): oid = Column( name = Column( image = Column( I don't want to query for that image. I just want to store it there for later showing t