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

[c.buhtz]

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 what
I want. ;)
I should never keep SQL out of my head.

This will help me a lot in the future!

-- 
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: order by child object's field

[c.buhtz]

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(ReferenceAuthor,
   ReferenceAuthor.c.Index==0) \
 .join(Person) \
 .order_by(Periodical._name) \
 .order_by(Person._lastname) \
 .all()



The SQL echoed looks ok:

SELECT "Reference"."ID" AS "Reference_ID",
   "Reference"."HasLabel" AS "Reference_HasLabel",
   "Reference"."PeriodicalID" AS "Reference_PeriodicalID"
FROM "Reference"
JOIN "Periodical"
ON "Periodical"."ID" = "Reference"."PeriodicalID"
JOIN "ReferenceAuthor"
ON "ReferenceAuthor"."Index" = ?
JOIN "Person"
ON "Person"."ID" = "ReferenceAuthor"."PersonID"
WHERE "Reference"."HasLabel" = 0
ORDER BY "Periodical"."Name",
 "Person"."LastName"

But result doesn't looks like that it is secondary sorted by first
persons lastname. See as an example one "Periodical._name" (Bmc
Geriatrics) and the first persons lastname of 8 References.

Bmc Geriatrics
  Dapp
  Tuntland
  Hsu
  van der Elst
  Khatib
  Hermans
  van Buul
  Lichtner

-- 
Verfassungsbeschwerden gegen Vorratsdatenspeicherung

Dein Recht zu unterzeichnen!

-- 
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: order by child object's field

[c.buhtz]

> 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) \
^

SyntaxError: keyword can't be an expression

Not sure what is wrong here. But I think c.Index doesn't exist?

-- 
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: order by child object's field

[c.buhtz]

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" can have more then one "Persons" (relation is
"ReferenceAuthor"). The "Persons" are orderd by an index
("ReferenceAuthor."Index").
How can I order by "Person._lastname" of only the FRIST author? I could
write something like this

  join(ReferenceAuthor, ReferenceAuthor.Index=0)

But my problem is that I don't know how to name/adress this index
field? As you can see in my first post this column doesn't have a
"name" (in pythonic meaning). It is just a Column("Index", ...).

-- 
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] order by child object's field

[c.buhtz]

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.

return self.session.query(Reference) \
 .filter_by(_mark = False) \
 #.order_by(paModel_sqla.Reference._periodical) \
 #.order_by(paModel_sqla.Reference._author._lastname) \
 .all()


This is the (simplified) model itself

ReferenceAuthor = sa.Table('ReferenceAuthor', _Base.metadata,
sa.Column('ReferenceID',  sa.Integer,
sa.ForeignKey('Reference.ID'), primary_key=True), sa.Column('PersonID',
sa.Integer, sa.ForeignKey('Person.ID'), primary_key=True),
sa.Column('Index', sa.Integer) )


class Reference(_Base):
__tablename__ = 'Reference'

_id = sa.Column('ID', sa.Integer, primary_key=True)
_mark = sa.Column('HasLabel', sa.Boolean)

# Autor
_authors = sao.relationship('Person', secondary=ReferenceAuthor,
order_by=ReferenceAuthor.c.Index)

# Journal
_periodical_fk = sa.Column('PeriodicalID',
sa.Integer,
sa.ForeignKey('Periodical.ID'))
_periodical = sao.relationship('Periodical')


class Periodical(_Base):
__tablename__ = 'Periodical'

_id = sa.Column('ID', sa.Integer, primary_key=True)
_name = sa.Column('Name', sa.String)


class Person(_Base):
__tablename__ = 'Person'

_id = sa.Column('ID', sa.Integer, primary_key=True)
_lastname = sa.Column('LastName', sa.String)
-- 
Verfassungsbeschwerden gegen Vorratsdatenspeicherung

Dein Recht zu unterzeichnen!

-- 
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] how to handle SQLite's GUID?

[c.buhtz]

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 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] how to handle SQLite's GUID?

[c.buhtz]

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 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] Use order_by when using relationship() with secondary-parameter in a many-to-many?

[c.buhtz]

> >> 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 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] Use order_by when using relationship() with secondary-parameter in a many-to-many?

[c.buhtz]

On 2016-02-14 08:38 Michael Bayer  wrote:
> order_by, you spelled it wrong.

Ah, I see the misstake. Was a copy 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 
"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] Use order_by when using relationship() with secondary-parameter in a many-to-many?

[c.buhtz]

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('PersonID',
sa.Integer, sa.ForeignKey('Person.ID'), primary_key=True),
sa.Column('Index', sa.Integer) )


class Reference(_Base):
__tablename__ = 'Reference'

_id = sa.Column('ID', sa.Integer, primary_key=True)

_authors = sao.relationship('Person', secondary=ReferenceAuthor)


class Person(_Base):
__tablename__ = 'Person'

_id = sa.Column('ID', sa.Integer, primary_key=True)

I would like to have the objects in `Reference().authors` ordered by
`ReferenceAuthor.Index`. The three tables here come from a foreign
sqlite3-database. I am not able to change the structure of that tables.
I have to deal with them like they are. I could finde some examples
about `order_by` in `relationship()` but nothing combined with the use
of `secondary=`.

I get the hint on stackoverflow [1] to use association_proxy. But I can
get it work.

[1]


-- 
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] visualize structure of the data

[c.buhtz]

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 tables?

You know a way?
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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.is_modified()

Is there a way to find out if the object in memory/RAM was modified
compared to its instance in the database itself? I don't want to
compare each attribute against the data in the dialog-window.
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] store a image(file)

[c.buhtz]

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 the picture of the person in a GUI.

What would be a way for that?

I found the "Binary" data type. But I don't know which concrete format
I should use for the image-data. I think each picture handling package
has its own raw-dataformat for things like that? e.g. wxPython use
wxImage as a "platform-independent" image format.

Or wouldn't it be easier and more flexible to store the whole
(png-)file like it is on the hard-drive to the database? Is it possible?
In that case I don't have to think about package-specific
binary-image-formats or things like that. That makes me more flexible
for the future with other packages/languages.

What do you think?
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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/array inside of TrainingUnit. I
wouldn't need to take care about relation tables or object ids - the
OODBMS make this for me.

But at this project I have no choice - I need an RDBMS.

On 2015-09-29 12:04 Mike Bayer  wrote:
> >  sqlite> SELECT a_oid, b_oid FROM a_b_relation;
> >  1|1
> >  1|1
> >
> > You see here that `a` has two references to the same entity/row `b`.
> > In my data structure this make sense. But maybe it break a
> > SQL-/RDBMS-rule?
> it does, it breaks first normal form in that there is no primary key 
> (http://www.tutorialspoint.com/sql/first-normal-form.htm).

But does a relation-table need a primary key? For me a relational-table
is just a helper-table (or a workaround) because RDBMS doesn't support
"native" 1-to-n or m-to-n references.
The normal-form-rules does have to fit to all(!) tables including
relation-tables?

> OK but those are not the same SetSet, they happened at two different 
> times.   They should be separate entities of data.

I understand what you mean. But the data in SetSet haven't a
time-dimension. The time (as a date) is coded in the TrainingUnit.
There is a attribute/row fort that.

> would like SetSet objects to be unique on their reps/weight, then the 
> association table itself would become an entity in which case you'd
> need to add an additional column to it as a primary key.

I am not really getting into that. Everytime i try to rearange my
current structure I ran into the same problem.

This is the real (little bit snipped) structure, where I can not see
how this would help me, when I add a primary-key to
the table named 'tu_set_relation'.


tu_set_relation = sa.Table('tu_set_relation', _Base.metadata,
sa.Column('tu_oid', sa.Integer, sa.ForeignKey('TrainingUnit.oid')),
sa.Column('set_oid', sa.Integer, sa.ForeignKey('SetSet.oid'))
)


class TrainingUnit(_Base):
__tablename__ = 'TrainingUnit'

_oid = sa.Column('oid', sa.Integer, primary_key=True)
_date = sa.Column('date', sa.Date)
_sets = sao.relationship('SetSet', secondary=tu_set_relation),
_machine_fk = sa.Column('machine', sa.Integer,
sa.ForeignKey('Machine.oid')) _machine = sao.relationship("Machine")


class SetSet(_Base):
__tablename__ = 'SetSet'# 'Set' is a reserved word in SQL

_oid = sa.Column('oid', sa.Integer, primary_key=True)
_repetitions = sa.Column('repetitions', sa.Integer)
_level = sa.Column('level', sa.Integer)
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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.Column('tu_oid', sa.Integer, sa.ForeignKey('TrainingUnit.oid')),
sa.Column('set_oid', sa.Integer, sa.ForeignKey('SetSet.oid'))
)
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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'?
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Handle multiple relations between two rows/objects

[c.buhtz]

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 have multible references to
entity `b` (from table/class `B`).

Sample:

sqlite> SELECT oid FROM A;
1
sqlite> SELECT oid, val FROM B;
1|0
2|1
sqlite> SELECT a_oid, b_oid FROM a_b_relation;
1|1
1|1

You see here that `a` has two references to the same entity/row `b`.
In my data structure this make sense. But maybe it break a
SQL-/RDBMS-rule?

When I do this in Python3 with the object it causes an error because
SQLAlchemy try to execute an DELETE statement for that.

a._bbb.clear()

The error

DELETE FROM a_b_relation WHERE a_b_relation.a_oid = ? AND
a_b_relation.b_oid = ? (1, 1)
ROLLBACK
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table
'a_b_relation' expected to delete 1 row(s); Only 2 were matched.

The error make sense for me in that case. But I don't know how I could
handle that.

Maybe it goes a bit to far but I will describe the original
data-structure, too. It is about making usage statistic for a gym. :)

You walk to a machine and lift some kilograms a couple of times - that
is called `TrainingUnit` (what is equivalent to `A` in the example).
For warming up you make 12 repetitions and lift up 35 kg in each of
them - that is called a `SetSet` (equivalent to `B` in the example;
keep in mind that `Set` is a reserved SQL-keyword). Then you make a
one-minute-break and do 12 repetitions with 40 kg - the second set. And
you do a third again(!) with 12 repetitions and 40 kg again. So the
`TrainingUnit` instance/row need three relations to instances/rows of
`SetSet`. Because the second and third set have the same
values/settings I would use the same instance/row of `SetSet` here.

Most of the time the sportsman does TrainingUnits each day in the same
configuration - which means each day the sets have the same values.
That is why I want to reuse that instances/rows of `SetSet`. And the
number of sets per TrainingUnit are not fixed - it depends on the
sportsman how many sets he/she will make.
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] how to find none-referenced objects

[c.buhtz]

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.Table('a_b_relation', _Base.metadata,
sa.Column('a_oid', sa.Integer, sa.ForeignKey('A.oid')),
sa.Column('b_oid', sa.Integer, sa.ForeignKey('B.oid'))
)


class A(_Base):
__tablename__ = 'A'

_oid = sa.Column('oid', sa.Integer, primary_key=True)
_bbb = sao.relationship('B', secondary=a_b_relation, backref='aaa')


class B(_Base):
__tablename__ = 'B'

_oid = sa.Column('oid', sa.Integer, primary_key=True)
k
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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 often
I don't use the correct search terms. This isn't for people speaking
like 6year olds. :)

I found a part of the docs


This looks like if it works.

session.query(B).filter(B.aaa == None).all()

This means I need the backref(). Would a query like this be possible
without it?
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] copy the scheme of a table

[c.buhtz]

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', metadata, Model.metadata.columns)

Creating a list of Column() and then use this list for the two tables
isn't a solution here for me. I need to ask one existing Table for
its column definitions and use them for a new one.
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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 word)
does alembic run the `env.py`. Maybe that causes some errors.

This is the directory and file structure I use.

myproject/
common/
__init__.py
model.py
alembic/
env.py

The error is kind of that

from .common import model
SystemError: Parent module '' not loaded, cannot perform relative
import

`myproject` itself is just a repository/working directory. It is not
*installed* into the system (with `pip3`, `apt-get`, `easyinstall` or
anything else).


  [1]: http://alembic.readthedocs.org/en/latest/autogenerate.html

-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

On 2015-08-11 21:43 Mike Bayer mike...@zzzcomputing.com wrote:
 a relationship() is not a Column.   Alembic add_column() is
 intended to render an ALTER TABLE foo ADD COLUMN bar
 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 alembic able for that or maybe there is another way to do that?
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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 as su

import alembic
import alembic.operations
import alembic.migration

_Base = sad.declarative_base()

# a simple model
class Model(_Base):
__tablename__ = 'Model'
_oid = sa.Column('oid', sa.Integer, primary_key=True)
_simple = sa.Column('simple', sa.Integer)

# engine
engine = sa.create_engine('sqlite:///mig2.db', echo = True)

# create database if needed
if not su.database_exists(engine.url):
su.create_database(engine.url)  # create the database
_Base.metadata.create_all(engine)   # create the table in the
db

# another model
class Sub(_Base):
__tablename__ = 'Sub'
_oid = sa.Column('oid', sa.Integer, primary_key=True)
_somedata = sa.Column('simple', sa.Integer)

# many-to-many-relation
model_sub_relation = sa.Table('model_sub_relation', _Base.metadata,
sa.Column('model_oid', sa.Integer, sa.ForeignKey('Model.oid')),
sa.Column('sub_oid', sa.Integer, sa.ForeignKey('Sub.oid'))
)

conn = engine.connect()
ctx = alembic.migration.MigrationContext.configure(conn)
op = alembic.operations.Operations(ctx)

op.create_table('Sub',
sa.Column('oid', sa.Integer, primary_key=True),
sa.Column('simple', sa.Integer)
  )

op.add_column('Model', sao.relationship('subs',
secondary=model_sub_relation, backref='Model'))



Here you see the error message.

Traceback (most recent call last):
  File ./mig2.py, line 54, in module
op.add_column('Model', sao.relationship('subs',
secondary=model_sub_relation, backref='Model')) File
/usr/local/lib/python3.4/dist-packages/alembic/operations.py, line
592, in add_column t = self._table(table_name, column, schema=schema)
File /usr/local/lib/python3.4/dist-packages/alembic/operations.py,
line 149, in _table t = sa_schema.Table(name, m, *columns, **kw) File
/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/schema.py, line
416, in __new__ metadata._remove_table(name, schema) File
/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py,
line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File
/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py,
line 182, in reraise raise value File
/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/schema.py, line
411, in __new__ table._init(name, metadata, *args, **kw) File
/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/schema.py, line
488, in _init self._init_items(*args) File
/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/schema.py, line
72, in _init_items item._set_parent_with_dispatch(self) File
/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py,
line 833, in __getattr__ return self._fallback_getattr(key) File
/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py,
line 811, in _fallback_getattr raise AttributeError(key)
AttributeError: _set_parent_with_dispatch
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop 

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

[c.buhtz]

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 stackoverflow.
http://stackoverflow.com/questions/31910185/alter-a-column-in-a-sqlite-database-from-an-integer-to-a-many-to-many-relationsh
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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 that technically and conceptual for an existing
sqlite3-database without losing data.

This should need to be done without hacking. This means maybe someone
still use an old version of the database in productive environment.
A new release of the used software now use another datastructure. This
new software should detected that there is an old-versioned database
and convert it to its new structure.

In the example above the 'field' could be changed into a relation to
another tabel or something more complex. It is just an example.
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

On 2015-08-02 19:15 c.bu...@posteo.jp 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
JSON-type field.

-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

I never used JSON before or know something about it. I will check that.

On 2015-08-03 14:38 Adam Tauno Williams awill...@whitemice.org 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 that. I couldn't find something like that e.g. for
sqlite3 and didn't remeber seeing something like that in PostgreSQL.

When I understand JSON right: On RDBMS level it is just a string.
JSON-data/string in a RDBMS field sounds for me like a hack, too.

If this works it is ok for me.
But I doesn't sound like real(!) support for lists or something like
that.

I have to say I worked a lot with real(!) ODBMS in the past. So my
thinking structure about data is always in that paradigma. This makes
it kind of hard for me to work with an RDBMS. :)
Lits and things like that are natural in ODBMS.

 SQLAlchemy has excellent support for 'advanced' data-types.

I looked there, too. Couldn't find a SQLAlchemy data type that looks
like a list. Maybe I missunderstand someting.
I overread JSON because I didn't know what this was.

btw: Am I right if I say I can build querys for specific fields in a
JSON-field?
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

On 2015-08-02 19:15 c.bu...@posteo.jp 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 sure how this would look like.

I need a list (undefined length) with tree values for each entry.

  x = [(1,2,3), (1,2,3), (1,2,3)]

-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

On 2015-08-03 01:17 Pavel S pa...@schon.cz 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 to know. Thank you very much.
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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 that VARCHAR-depending converting work?

And I can not found something like thist over https://duckduckgo.com.
But I imagine that some persons in the past invited something like that.
Any hints?
-- 
-BEGIN PGP PUBLIC KEY BLOCK-
Version: GnuPG v1

mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] func.substr() documentation?

[c.buhtz]

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
G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH
kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3
0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe
GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33
LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0
eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH
AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj
DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY
Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK
D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U
jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi
du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA
UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0
KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B
zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+
VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj
v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p
+ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU
CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF
blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h
2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW
sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A
dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm
lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk=
=PGP9
-END PGP PUBLIC KEY BLOCK-

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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 `class 'float'`). 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 Model(_Base):
__tablename__ = 'Model'
...
_weight = sa.Column('weight', sa.Numeric(10, 2))
...

query = session.query(Model._weight)
result = query.all()
print(type(result))
print(type(result[0]))
print(type(result[0][0]))

This is the result I can not work with

class 'list'
class 'sqlalchemy.util._collections.result'
class 'decimal.Decimal'

When I use `Numeric(10, 2, asdecimal=False)` and `int` is returned. I
know that sqlite can not handle float numbers.

btw. I want to work with that float numbers in matplotlib.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] supress SAWarning about decimal numbers

[c.buhtz]

On 2015-07-11 01:21 c.bu...@posteo.jp 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.filterwarnings('ignore', message=msg)

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] supress SAWarning about decimal numbers

[c.buhtz]

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?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

X-Post on stackoverflow: http://stackoverflow.com/posts/31307677

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 unsupported type.
SQL: 'INSERT INTO TrainingUnit
(oid, order, date, level, sets, repetitions, machine)
VALUES (?, ?, ?, ?, ?, ?, ?)']
[parameters: (6, 0, '2014-12-12', Decimal('75'), None, None, 1)]

I understand that parameter 3 means the fourth one because the
counting begins with 0. I think the problem here is the `Decimal()`. I
don't understand why SQLAlchemy add this here? What is this
'Decimal()'? Is it Python, SQL, ...?

The scheme itself is generated automaticly from SQLA with that statement

CREATE TABLE TrainingUnit (
oid INTEGER NOT NULL, 
order INTEGER, 
date DATE, 
level INTEGER, 
sets INTEGER, 
repetitions INTEGER, 
machine INTEGER, 
PRIMARY KEY (oid), 
FOREIGN KEY(machine) REFERENCES Machine (oid)
)

...out of that SQLAlchemy/Python class definition

class TrainingUnit(_Base):
__tablename__ = 'TrainingUnit'

_oid = sa.Column('oid', sa.Integer, primary_key=True)
_order = sa.Column('order', sa.Integer)
_date = sa.Column('date', sa.Date)
_level = sa.Column('level', sa.Integer)
_sets = sa.Column('sets', sa.Integer)
_repetitions = sa.Column('repetitions', sa.Integer)
_machine_fk = sa.Column('machine', sa.Integer,
sa.ForeignKey('Machine.oid')) _machine = sao.relationship(Machine)
...

This is a part of the `add()` using code. It copy/convert objects
from a PostgreSQL database to a SQLite database using SQLAlchemy. This
worked fine for two other tables.

pstr = 'postgres://puser@localhost/' + dbname
sstr = 'sqlite:///' + dbname + '.db'
pengine = sa.create_engine(pstr, echo = False)
sengine = sa.create_engine(sstr, echo = True)

# create and open the SQLite database
create_database(sengine.url)
mod._Base.metadata.create_all(sengine)
ssession = sao.sessionmaker(bind = sengine)()

# open the PostgreSQL database
psession = sao.sessionmaker(bind = pengine)()

# ...

# TrainingUnit
for t in psession.query(mod.TrainingUnit).all():
sas.make_transient(t)
ssession.add(t)
ssession.commit()

psession.close()
ssession.close()

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] convert PostgreSQL to sqlite

[c.buhtz]

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 sql-echo-output about that object. Nothing happens when I try to
add() it. The table schemes are created fine.

What could there go on?

[code]
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import model as m
import sqlalchemy as sa
import sqlalchemy.orm as sao
from sqlalchemy_utils import create_database

pstr = 'postgres://puser@localhost/Foobar'
sstr = 'sqlite:///foobar.db'

def convert():
# get one object from the PostgreSQL database
pengine = sa.create_engine(pstr, echo = True)
psession = sao.sessionmaker(bind = pengine)()
pobj = psession.query(m.Machine).first()
psession.expunge(pobj)
psession.close()

# create and open the SQLite database
sengine = sa.create_engine(sstr, echo = True)
create_database(sengine.url)
m._Base.metadata.create_all(sengine)

# add/convert the one object to the new database
ssession = sao.sessionmaker(bind = sengine)()
ssession.add(pobj)

# add a new created object to the new database
nobj = m.Machine(code='X', name='Mr. X', note='bähm')
ssession.add(nobj)
ssession.commit()

if __name__ == '__main__':
convert()
[/code]

Would you like to see the SQL-output? Beside the table Machine there
are two other tables in it - just ignore them.
[sql]
2015-07-09 00:24:45,088 INFO sqlalchemy.engine.base.Engine select
version() 2015-07-09 00:24:45,089 INFO sqlalchemy.engine.base.Engine {}
2015-07-09 00:24:45,096 INFO sqlalchemy.engine.base.Engine select
current_schema() 2015-07-09 00:24:45,097 INFO
sqlalchemy.engine.base.Engine {} 2015-07-09 00:24:45,102 INFO
sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS
VARCHAR(60)) AS anon_1 2015-07-09 00:24:45,102 INFO
sqlalchemy.engine.base.Engine {} 2015-07-09 00:24:45,107 INFO
sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS
VARCHAR(60)) AS anon_1 2015-07-09 00:24:45,107 INFO
sqlalchemy.engine.base.Engine {} 2015-07-09 00:24:45,110 INFO
sqlalchemy.engine.base.Engine show standard_conforming_strings
2015-07-09 00:24:45,111 INFO sqlalchemy.engine.base.Engine {}
2015-07-09 00:24:45,119 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit) 2015-07-09 00:24:45,124 INFO sqlalchemy.engine.base.Engine
SELECT Machine.oid AS Machine_oid, Machine.code AS
Machine_code, Machine.name AS Machine_name, Machine.note AS
Machine_note FROM Machine LIMIT %(param_1)s 2015-07-09 00:24:45,125
INFO sqlalchemy.engine.base.Engine {'param_1': 1} 2015-07-09
00:24:45,185 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain
returns' AS VARCHAR(60)) AS anon_1 2015-07-09 00:24:45,186 INFO
sqlalchemy.engine.base.Engine () 2015-07-09 00:24:45,188 INFO
sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS
VARCHAR(60)) AS anon_1 2015-07-09 00:24:45,189 INFO
sqlalchemy.engine.base.Engine () 2015-07-09 00:24:45,191 INFO
sqlalchemy.engine.base.Engine PRAGMA table_info(PersonalParameter)
2015-07-09 00:24:45,192 INFO sqlalchemy.engine.base.Engine ()
2015-07-09 00:24:45,194 INFO sqlalchemy.engine.base.Engine PRAGMA
table_info(Machine) 2015-07-09 00:24:45,195 INFO
sqlalchemy.engine.base.Engine () 2015-07-09 00:24:45,196 INFO
sqlalchemy.engine.base.Engine PRAGMA table_info(TrainingUnit)
2015-07-09 00:24:45,197 INFO sqlalchemy.engine.base.Engine ()
2015-07-09 00:24:45,203 INFO sqlalchemy.engine.base.Engine CREATE TABLE
PersonalParameter ( oid INTEGER NOT NULL, date DATE, weight
NUMERIC(10, 2), PRIMARY KEY (oid) )


2015-07-09 00:24:45,204 INFO sqlalchemy.engine.base.Engine ()
2015-07-09 00:24:45,377 INFO sqlalchemy.engine.base.Engine COMMIT
2015-07-09 00:24:45,381 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE Machine (
oid INTEGER NOT NULL, 
code VARCHAR, 
name VARCHAR, 
note VARCHAR, 
PRIMARY KEY (oid)
)


2015-07-09 00:24:45,388 INFO sqlalchemy.engine.base.Engine ()
2015-07-09 00:24:45,567 INFO sqlalchemy.engine.base.Engine COMMIT
2015-07-09 00:24:45,572 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE TrainingUnit (
oid INTEGER NOT NULL, 
order INTEGER, 
date DATE, 
level INTEGER, 
sets INTEGER, 
repetitions INTEGER, 
machine INTEGER, 
PRIMARY KEY (oid), 
FOREIGN KEY(machine) REFERENCES Machine (oid)
)


2015-07-09 00:24:45,584 INFO sqlalchemy.engine.base.Engine ()
2015-07-09 00:24:45,777 INFO sqlalchemy.engine.base.Engine COMMIT
2015-07-09 00:24:45,785 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit) 2015-07-09 00:24:45,793 INFO sqlalchemy.engine.base.Engine
INSERT INTO Machine (code, name, note) VALUES (?, ?, ?) 2015-07-09
00:24:45,795 INFO sqlalchemy.engine.base.Engine ('X', 'Mr. X', 'bähm')
2015-07-09 00:24:45,802 INFO sqlalchemy.engine.base.Engine 

Re: [sqlalchemy] convert PostgreSQL to sqlite

[c.buhtz]

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.session as sas
from sqlalchemy_utils import create_database

_Base = sad.declarative_base()

class Child(_Base):
__tablename__ = 'Child'

_oid = sa.Column('oid', sa.Integer, primary_key=True)
_name = sa.Column('name', sa.String)

def __init__(self, name):
self._name = name


class Parent(_Base):
__tablename__ = 'Parent'

_oid = sa.Column('oid', sa.Integer, primary_key=True)
_name = sa.Column('name', sa.String)
_child_fk = sa.Column('child', sa.Integer,
sa.ForeignKey('Child.oid')) _child = sao.relationship('Child')

def __init__(self, name):
super(Parent, self).__init__()
self._name = name


pstr = 'postgres://postgres@localhost/Family'
sstr = 'sqlite:///family.db'

pengine = sa.create_engine(pstr, echo = True)
sengine = sa.create_engine(sstr, echo = True)

def createPostgreSQL_Family():
Create for PostgreSQL the scheme and the data for testing.

# create schema
create_database(pengine.url)
_Base.metadata.create_all(pengine)
psession = sao.sessionmaker(bind = pengine)()

# child
c = Child('Jim Bob')
psession.add(c)
psession.commit()

# parent
p = Parent('Mr. Doe')
p._child = c
psession.add(p)
psession.commit()

psession.close()

def convert():
# get one object from the PostgreSQL database
psession = sao.sessionmaker(bind = pengine)()

p = psession.query(Parent).first()
sas.make_transient(p)
#p._oid = None

c = psession.query(Child).first()
sas.make_transient(c)
#c._oid = None

psession.close()

# create and open the SQLite database
create_database(sengine.url)
_Base.metadata.create_all(sengine)

# add/convert the one object to the new database
ssession = sao.sessionmaker(bind = sengine)()

ssession.add(c)
ssession.add(p)
ssession.commit()

if __name__ == '__main__':
#createPostgreSQL_Family()
convert()

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] convert PostgreSQL to sqlite

[c.buhtz]

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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] convert PostgreSQL to sqlalchemy

[c.buhtz]

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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] create/restore a dump-file

[c.buhtz]

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 when I want to use my application portable I think it would be a
good workaround to create a dump-file of the database when the
application is closed and restore it when it is started. So application
doesn't have to think about on which machine it is running.

I could access PostgreSQL directly on commandline but it is not
platformindependent. I want to do it with the sqlalchemy-way.

Would the Serializer Extension be a good solution for that?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] subscribe

[c.buhtz]

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

On 2015-06-05 20:53 c.bu...@posteo.jp 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'
_oid = sqlalchemy.Column('oid', sqlalchemy.Integer,
primary_key=True)


model = Model()

# of course this is False
print ( Base == sqlalchemy.ext.declarative.declarative_base() )

# this can not work even
print( isinstance( model, sqlalchemy.ext.declarative.declarative_base()
) )

# but 'Base' is unknown at this point
# I need a generalized way to find out if a given instance is mapped
# by SQLAlchemy
[/code]

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

On 2015-06-05 20:53 c.bu...@posteo.jp 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 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] is attribute a Foreign- or PrimaryKey?

[c.buhtz]

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 for me. e.g. The key attribute is
not in my documentation.
file:///usr/share/doc/python-sqlalchemy-doc/html/orm/internals.html#sqlalchemy.orm.state.AttributeState

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

On 2015-05-26 13:06 Mike Bayer mike...@zzzcomputing.com 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 the user did it in a dialog) I want to save
that changes to the database.

I am not sure if I have to call add() or merge() for that.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Session.close() implicite

[c.buhtz]

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.orm as sao

_engine = sa.create_engine('postgres://...')

class BasicController():


def __init__(self):
self._session = None

def CreateSession(self):
if self._session is not None:
raise AttributeError('...')

self._session = sao.sessionmaker(bind=_engine)()


@property
def session(self):


if not self._session:
self.CreateSession()

return self._session

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] ...and MVC

[c.buhtz]

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 current solution I have controllers holding a Session object. The
controller does the add(), commit(), rollback() stuff.

The models can be all declaraive_base() derived classes.

How do you realize this?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Session.close() implicite

[c.buhtz]

On 2015-05-25 11:12 Mike Bayer mike...@zzzcomputing.com 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 such.

I am quite new to Python and not familiar with the decorator concept.
What do you mean with memoized thing? And why could the decorators
help me here?

 As for the session would be destroyed/closed, I'd never rely on
 that,

I see. The link helped.
Of course the Gui-Dialog related to such a Controller instance will
call a SessionCommit() ('OK' is pressed) or SessionRollback() ('Cancel'
is pressed). So the controller instance itself call commit() or
rollback() on its session object.

Maybe you could implement a BasicController.__del__() to check the
state of the Session if it is commited or rolled back. I mean if
someone missed to call commit() or rollback() on int.
But I am not sure if it is possible to ask a Session object for things
like that. What do you think?


 that's a poor design, and it will lead to problems like
 connection pool overflows and deadlocks.

I understand that. I thought the Session object would be destroyed
automaticly when the controller is destroyed.
And I think the Session take care of itself to release the resources
while it is destroyed.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

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 reference cycle
counting stuff in the background of SQLA. But how can I prevent this?

obj = GetObjFromDb()

make_transient(obj)
obj._pk = None

# This can return 'None' in some cases
obj.GetForeignKeyObject()

I found this out because of some debug messages put out with print().
When I call print(obj) (this implicit a GetForeignKeyObject()) BEFORE
make_transient() it doesn't lose the reference to its
foreign-key-object. :D

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

On 2015-05-23 18:30 c.bu...@posteo.jp 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 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

On 2015-05-23 18:30 c.bu...@posteo.jp 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
missunderstood this parameter.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

Hi Mike,

thanks for your help.

On 2015-05-22 02:32 Mike Bayer mike...@zzzcomputing.com 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 will be used instead.

This works for the case.

But I wonder...
When I create a really new object, add() it to the session, I don't have
to call flush() on the session before commit() it.

The add-and-commit-part of my application doesn't know where the object
comes from. It doesn't know if it is real new or if it is a duplicate
created with make_transient() and Primary_Key=None.

See what I mean?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

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

[c.buhtz]

I opened a questions with example (pseudo) code on stackoverflow for
that.
http://stackoverflow.com/questions/30287042/how-to-use-make-transient-to-duplicate-an-sqlalchemy-mapped-object

I know the question how to duplicate or copy a SQLAlchemy mapped object
was asked a lot of times. The answer always depends on the needs or how
duplicate or copy is interpreted. This is a specialized version of
the question because I got the tip to use make_transient() for that.

But I have some problems with that. I don't really know how to handle
the primary key (PK) here. In my use cases the PK is always
autogenerated by SQLA (or the DB in background). But this doesn't
happen with a new duplicated object.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.