Re: [sqlalchemy] inserting into many to many relation
you would need to query the database for an already existing Keyword object
first. I.e.:
existing_keyword = Session.query(Keyword).filter(Keyword.name=='test
image').first()
if existing_keyword is None:
existing_keyword = Keyword(name='test image')
There's a recipe that can help with the "create object if doesn't exist"
paradigm. That is at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject .
On Nov 18, 2010, at 12:56 PM, Sujeevan Rasaratnam wrote:
> Hello,
> I am new to SQLAlchemy and I couldn't find a way to properly associate many
> to many relation. My environment: python 2.6, SQLAlchemy 0.6.5, Postgres
> 8.4.5. How do I associate to a already existing row in many to many relation.
>
>
> class Keyword(Base):
> __tablename__ = 'keywords'
>
> id = Column(Integer, primary_key=True, autoincrement=True)
> name = Column(Unicode, unique=True, nullable=False)
> description = Column(Unicode, nullable=True)
>
> class ImageKeyword(Base):
> __tablename__ = 'image_keywords
> image_id = Column(Integer, ForeignKey('images.id', onupdate = 'CASCADE',
> ondelete = 'CASCADE'), primary_key=True)
> keyword_id = Column(Integer, ForeignKey('keywords.id', onupdate =
> 'CASCADE',
> ondelete = 'CASCADE'), primary_key=True)
>
> class Image(Base):
> __tablename__ = "images"
> id = Column(Integer, primary_key=True)
> name = Column(Unicode, unique=True, nullable=False)
>
> keywords = relationship(Image,
> secondary="image_keyword",
> backref='images')
> def addKeywords(self, keywords):
> for keyword in keywords:
> self.keywords.append(Keyword(name=keyword))
>
> # Add
> session = Session()
> i = Image()
> i.name = "image1"
> i.addKeyword(['test image', 'first image'])
> session.add(i)
> session.commit()
>
> i = Image()
> i.name = "image2"
> i.addKeyword(['test image'])
> session.add(i)
> session.commit()
> #This will fail because of the unique constraint of Keyword.name
> # What is the proper way?
>
> #
>
> Thank you
>
>
> Sujeevan Rasaratnam
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Weird error when adding a new object
looks fine to me, would need full stack trace + single script file of runnable,
reproducing code, thanks.
On Nov 18, 2010, at 6:37 PM, Alvaro Reinoso wrote:
> Hello,
>
> When trying to add a new item doing:
>
> session = session.add(mediaGroup)
>
> I got this error:
>
> Module sqlalchemy.orm.attributes:834 in get_collection
> return getattr(user_data, '_sa_adapter')
> class GenericBackrefExtension(interfaces.AttributeExtension):>>
> return getattr(user_data, '_sa_adapter')
>
> "AttributeError: 'list' object has no attribute '_sa_adapter'"
>
> This object's class:
>
> class ScreenGroup(rdb.Model):
> """The ScreenGroup is a class derived from ScreenGroup, it is used to
> control users within a group"""
> rdb.metadata(metadata)
> rdb.tablename("screen_groups")
>
> id = Column("id", Integer, primary_key=True)
> title = Column("title", String(100))
> parents = Column("parents", String(512))
>
> screens = relationship("Screen", secondary=group_screens,
> order_by="Screen.title", backref="screen_groups")
> screenGroups = relationship("ScreenGroup",
> secondary=screen_group_groups, order_by="ScreenGroup.title",
> primaryjoin=lambda: ScreenGroup.id ==
> screen_group_groups.c.screen_groupA_id,
> secondaryjoin=lambda: ScreenGroup.id ==
> screen_group_groups.c.screen_groupB_id,
> backref="screen_groups")
>
> Thanks in advance!
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Weird error when adding a new object
Hello,
When trying to add a new item doing:
session = session.add(mediaGroup)
I got this error:
Module sqlalchemy.orm.attributes:834 in get_collection
return getattr(user_data, '_sa_adapter')
class GenericBackrefExtension(interfaces.AttributeExtension):>>
return getattr(user_data, '_sa_adapter')
"AttributeError: 'list' object has no attribute '_sa_adapter'"
This object's class:
class ScreenGroup(rdb.Model):
"""The ScreenGroup is a class derived from ScreenGroup, it is used to
control users within a group"""
rdb.metadata(metadata)
rdb.tablename("screen_groups")
id = Column("id", Integer, primary_key=True)
title = Column("title", String(100))
parents = Column("parents", String(512))
screens = relationship("Screen", secondary=group_screens,
order_by="Screen.title", backref="screen_groups")
screenGroups = relationship("ScreenGroup",
secondary=screen_group_groups, order_by="ScreenGroup.title",
primaryjoin=lambda: ScreenGroup.id ==
screen_group_groups.c.screen_groupA_id,
secondaryjoin=lambda: ScreenGroup.id ==
screen_group_groups.c.screen_groupB_id,
backref="screen_groups")
Thanks in advance!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] ProgrammingError: (ProgrammingError) can't adapt "INSERT INTO
its likely the column it's having problems with is the GeometryColumn - you
should check with the GeoAlchemy list:
http://groups.google.com/group/geoalchemy
though if I were to guess it appears the call to POLYGON has an extra
parenthesis in it. Like if you said x=((1, 2, 3, 4),) or something like that.
On Nov 18, 2010, at 2:55 PM, wilbur wrote:
> Hello,
>
> I am having problems inserting data into a postgres table. My model
> definition:
>
> *
>
> class dream8_eta_50km_pm25(Base):
>__tablename__='dream8_eta_50km_pm25'
>
> oid=Column(Integer,Sequence('dream8_eta_50km_pm25_oid_seq'),primary_key=True)
>rundate=Column(DateTime(timezone=True),nullable=False)
>datetime=Column(DateTime(timezone=True),nullable=False)
>location_raw=Column(VARCHAR,nullable=False)
>location_reclass=Column(VARCHAR,nullable=False)
>timezone=Column(DateTime(timezone=False),nullable=False)
>the_geom=GeometryColumn(Polygon(2),nullable=False)
>max_pm25=Column(Float,nullable=False)
>mean_pm25=Column(Float,nullable=False)
>
> GeometryDDL(dream8_eta_50km_pm25.__table__)
>
> ***
> PLUS MY POSTGRESQL TABLE DEFINITION..
>
> enphasys=# \d dream8_eta_50km_pm25
> Table
> "public.dream8_eta_50km_pm25"
> Column |Type
> | Modifiers
> --+-
> +
> oid | integer | not null default
> nextval('dream8_eta_50km_pm25_oid_seq'::regclass)
> rundate | timestamp with time zone| not null
> datetime | timestamp with time zone| not null
> location_raw | character varying | not null
> location_reclass | character varying | not null
> timezone | timestamp without time zone | not null
> max_pm25 | double precision| not null
> mean_pm25| double precision| not null
> the_geom | geometry| not null
> Indexes:
>"dream8_eta_50km_pm25_pkey" PRIMARY KEY, btree (oid)
>"idx_dream8_eta_50km_pm25_the_geom" gist (the_geom)
> Check constraints:
>"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
>"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
> 'POLYGON'::text OR the_geom IS NULL)
>"enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
> ***
>
> GIVES ME THE FOLLOWING ERROR:
>
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt
> "INSERT INTO dream8_eta_50km_pm25 (oid, rundate, datetime,
> location_raw, location_reclass, timezone, the_geom, max_pm25,
> mean_pm25) VALUES (nextval('dream8_eta_50km_pm25_oid_seq'), %
> (rundate)s, %(datetime)s, %(location_raw)s, %(location_reclass)s, %
> (timezone)s, GeomFromText(%(GeomFromText_1)s, %(GeomFromText_2)s), %
> (max_pm25)s, %(mean_pm25)s) RETURNING
> dream8_eta_50km_pm25.oid" {'location_reclass': '/geodata/arizona_dream/
> eta_dream8/dream_reclass_tiff/20100901/
> mcd12_aug0410_eta_dream8_pm25_50km_100901_f00.tif', 'GeomFromText_2':
> 4326, 'location_raw': '/geodata/arizona_dream/eta_dream8/
> dream_raw_tiff/20100901/
> mcd12_aug0410_eta_dream8_pm25_50km_100901_f00.tif', 'GeomFromText_1':
> 'POLYGON((-116.000 40.000,-103.000 40.000,-103.000 30.000,-116.000
> 30.000,-116.000 40.000))', 'mean_pm25': 4.7778698952101335,
> 'datetime': '2010-09-01 00:00:00Z', 'timezone': '2010-09-01 00:00:00',
> 'max_pm25': 29.3267301, 'rundate': '2010-09-01 00:00:00Z'}
>
> ANY INSIGHT WOULD BE GREATLY APPRECIATED
>
> THANKS, BILL
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] ProgrammingError: (ProgrammingError) can't adapt "INSERT INTO
Hello,
I am having problems inserting data into a postgres table. My model
definition:
*
class dream8_eta_50km_pm25(Base):
__tablename__='dream8_eta_50km_pm25'
oid=Column(Integer,Sequence('dream8_eta_50km_pm25_oid_seq'),primary_key=True)
rundate=Column(DateTime(timezone=True),nullable=False)
datetime=Column(DateTime(timezone=True),nullable=False)
location_raw=Column(VARCHAR,nullable=False)
location_reclass=Column(VARCHAR,nullable=False)
timezone=Column(DateTime(timezone=False),nullable=False)
the_geom=GeometryColumn(Polygon(2),nullable=False)
max_pm25=Column(Float,nullable=False)
mean_pm25=Column(Float,nullable=False)
GeometryDDL(dream8_eta_50km_pm25.__table__)
***
PLUS MY POSTGRESQL TABLE DEFINITION..
enphasys=# \d dream8_eta_50km_pm25
Table
"public.dream8_eta_50km_pm25"
Column |Type
| Modifiers
--+-
+
oid | integer | not null default
nextval('dream8_eta_50km_pm25_oid_seq'::regclass)
rundate | timestamp with time zone| not null
datetime | timestamp with time zone| not null
location_raw | character varying | not null
location_reclass | character varying | not null
timezone | timestamp without time zone | not null
max_pm25 | double precision| not null
mean_pm25| double precision| not null
the_geom | geometry| not null
Indexes:
"dream8_eta_50km_pm25_pkey" PRIMARY KEY, btree (oid)
"idx_dream8_eta_50km_pm25_the_geom" gist (the_geom)
Check constraints:
"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
'POLYGON'::text OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
***
GIVES ME THE FOLLOWING ERROR:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt
"INSERT INTO dream8_eta_50km_pm25 (oid, rundate, datetime,
location_raw, location_reclass, timezone, the_geom, max_pm25,
mean_pm25) VALUES (nextval('dream8_eta_50km_pm25_oid_seq'), %
(rundate)s, %(datetime)s, %(location_raw)s, %(location_reclass)s, %
(timezone)s, GeomFromText(%(GeomFromText_1)s, %(GeomFromText_2)s), %
(max_pm25)s, %(mean_pm25)s) RETURNING
dream8_eta_50km_pm25.oid" {'location_reclass': '/geodata/arizona_dream/
eta_dream8/dream_reclass_tiff/20100901/
mcd12_aug0410_eta_dream8_pm25_50km_100901_f00.tif', 'GeomFromText_2':
4326, 'location_raw': '/geodata/arizona_dream/eta_dream8/
dream_raw_tiff/20100901/
mcd12_aug0410_eta_dream8_pm25_50km_100901_f00.tif', 'GeomFromText_1':
'POLYGON((-116.000 40.000,-103.000 40.000,-103.000 30.000,-116.000
30.000,-116.000 40.000))', 'mean_pm25': 4.7778698952101335,
'datetime': '2010-09-01 00:00:00Z', 'timezone': '2010-09-01 00:00:00',
'max_pm25': 29.3267301, 'rundate': '2010-09-01 00:00:00Z'}
ANY INSIGHT WOULD BE GREATLY APPRECIATED
THANKS, BILL
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] inserting into many to many relation
Hello,
I am new to SQLAlchemy and I couldn't find a way to properly associate many to
many relation. My environment: python 2.6, SQLAlchemy 0.6.5, Postgres 8.4.5.
How do I associate to a already existing row in many to many relation.
class Keyword(Base):
__tablename__ = 'keywords'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(Unicode, unique=True, nullable=False)
description = Column(Unicode, nullable=True)
class ImageKeyword(Base):
__tablename__ = 'image_keywords
image_id = Column(Integer, ForeignKey('images.id', onupdate = 'CASCADE',
ondelete = 'CASCADE'), primary_key=True)
keyword_id = Column(Integer, ForeignKey('keywords.id', onupdate =
'CASCADE',
ondelete = 'CASCADE'), primary_key=True)
class Image(Base):
__tablename__ = "images"
id = Column(Integer, primary_key=True)
name = Column(Unicode, unique=True, nullable=False)
keywords = relationship(Image,
secondary="image_keyword",
backref='images')
def addKeywords(self, keywords):
for keyword in keywords:
self.keywords.append(Keyword(name=keyword))
# Add
session = Session()
i = Image()
i.name = "image1"
i.addKeyword(['test image', 'first image'])
session.add(i)
session.commit()
i = Image()
i.name = "image2"
i.addKeyword(['test image'])
session.add(i)
session.commit()
#This will fail because of the unique constraint of Keyword.name
# What is the proper way?
#
Thank you
Sujeevan Rasaratnam
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Encoding issue with SqlAlchemy + cxOracle
SQLAlchemy can do the encoding for you as Anthony suggests, in this case if you
use the Unicode() type with the flag "convert_unicode='force'". Unfortunately
the 'force' value isn't accepted at the create_engine() level (thought it was,
apparently not).Its likely that WITH_UNICODE is the easier route for you at
the moment.
On Nov 18, 2010, at 11:31 AM, Guilherme Menezes wrote:
> An update:
>
> It seems that cx_Oracle always sends data in UTF-16 if WITH_UNICODE is
> unset and unicode() objects are passed.
>
> http://sourceforge.net/mailarchive/message.php?msg_name=AANLkTinTVdfPCn15HgXL6zo7cvRdSoE1t0_Z4eqXTGmQ%40mail.gmail.com
>
> Regards,
> Guilherme.
>
> On Wed, Nov 17, 2010 at 2:16 PM, Michael Bayer
> wrote:
>>
>> On Nov 17, 2010, at 10:26 AM, Guilherme Menezes wrote:
>>
>>> Hi,
>>>
>>> I am having an encoding issue using Python 2.6.2, SqlAlchemy 0.6.5 and
>>> cxOracle 5.0.4 to access an Oracle 11g database.
>>>
>>> I am using the NLS_LANG=.AL32UTF8 enviroment variable. My table in
>>> Oracle is correctly configured to accept Unicode.
>>>
>>> First, I compiled cxOracle without the WITH_UNICODE flag (as
>>> recommended by SqlAlchemy). I was passing data to SqlAlchemy as
>>> unicode() objects. Everything worked without errors or warnings.
>>>
>>> However, sometimes Oracle would complain that the string I was trying
>>> to insert into a VARCHAR2 field was too big (> 4000), even when the
>>> string size ( len(the_string.encode('utf-8')) ) was about 2300 bytes
>>> (before SqlAlchemy). I used a sniffer to verify that the Oracle client
>>> was sending two bytes for each character (even the ASCII ones),
>>> instead of sending two bytes only for special characters.
>>>
>>> I repeated this insertion experiment using cx_Oracle directly and
>>> passing unicode() objects to it. Same thing: no errors or warnings,
>>> and again the sniffer shown that the Oracle client was sending two
>>> bytes per character. It seemed to me that cx_Oracle does not really
>>> supports unicode() objects; it accepts them but it does not encode()
>>> them to the correct encoding (as set in NLS_LANG variable).
>>>
>>> However, when reading data from Oracle everything worked as expected
>>> (i.e. someone was converting the UTF-16(?) string to UTF-8, probably
>>> Oracle itself).
>>>
>>> I tried two approaches to make the Oracle client send data in UTF-8
>>> (not UTF-16):
>>>
>>> 1. Passing str() objects to SqlAlchemy. SqlAlchemy would then complain
>>> in a warning:
>>> * UnicodeWarning: Unicode equal comparison failed to convert both
>>> arguments to Unicode - interpreting them as being unequal *
>>> It worked, but it did not seem right, since I was receiving unicode()
>>> objects and sending str() objetcs to SqlAlchemy. Moreover, I did not
>>> know how things work inside SqlAlchemy, so I tried something else.
>>>
>>> 2. Compiling cx_Oracle with the WITH_UNICODE flag solved the problem
>>> (utf-8 was sent to Oracle), but another warning was issued:
>>> * SAWarning: cx_Oracle is compiled under Python 2.xx using the
>>> WITH_UNICODE flag. Consider recompiling cx_Oracle without this flag,
>>> which is in no way necessary for full support of Unicode. Otherwise,
>>> all string-holding bind parameters must be explicitly typed using
>>> SQLAlchemy's String type or one of its subtypes,or otherwise be passed
>>> as Python unicode. Plain Python strings passed as bind parameters
>>> will be silently corrupted by cx_Oracle. *
>>>
>>> Right now I am using cx_Oracle compiled with WITH_UNICODE, everything
>>> works, but this warning is worrying me. I tried to pass a str() object
>>> in UTF-8 to SqlAlchemy, and the execution halted with an encoding
>>> error (UnicodeDecodeError). In other words, cx_Oracle did not silently
>>> corrupted the data (at least when special characters were sent).
>>>
>>> I would like to know if anyone has a better (safer) solution to my
>>> problem. Or am I safe enough by using the WITH_UNICODE flag and
>>> passing only unicode() objects?
>>
>>
>> Regarding the passing of utf-8 vs. utf-16 and the differences in
>> WITH_UNICODE mode, you should really email the cx_oracle list about that as
>> WITH_UNICODE to my knowledge only has to do with the Python interpretation
>> of arguments, not its interaction with OCI. If you email their list, make
>> sure all code examples are purely derived from cx_oracle, to eliminate any
>> doubt that each behavior is one of cx_oracle directly. WITH_UNICODE was not
>> intended for general use in Python 2.xx. if your whole application really
>> works with it, then there's no reason not to use it, its just that you'll
>> always have to ensure that no non-Python unicode strings ever get sent to
>> cx_oracle which can be fairly tedious. It sounds like a bug that cx_oracle
>> would be expanding into a two-byte-per-character stream like that.
>>
>> Its possible that calling setinputsizes() with cx_oracle.UNICODE may be the
>> key to cx_oracle's behavior
Re: [sqlalchemy] Something like orderinglist for secondary tables?
On Nov 18, 2010, at 11:01 AM, Torsten Landschoff wrote: > Hi Michael, > > Thanks for your lightning fast reply! > > On Thu, 2010-11-18 at 10:17 -0500, Michael Bayer wrote: > >> this is correct. The functionality provided by "secondary" is that SQLA >> will maintain a table with foreign keys to the related primary keys on >> either side. It does not do anything at all with additional columns on the >> "secondary" table. If your "secondary" table has additional columns you >> need to deal with, you no longer use "secondary" and instead use the >> association object pattern : >> http://www.sqlalchemy.org/docs/orm/relationships.html#association-object . >> To make this pattern act more like "secondary" in the usual case, you use >> associationproxy: >> http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html >> >> You could most likely use the existing orderinglist extension in conjunction >> with associationproxy to maintain the ordering you want, in Python. > > Okay, I updated my example code and it actually works now. However, it > feels like a lot of additional complexity just for adding order. Hm, I just looked at the script and compared it to your previous two pages of Mapper/Session extensions, seems a lot simpler to me ! The relationship + secondary approach does support "ordering" by an extra column in the association table, it just doesn't support direct mutation of that value. The rationale for the association object pattern is that it is a consistent way of establishing full control over the "secondary" table, using the exact same paradigms as that which grant control over the rows of any other table. If we did it via flags and switches to relationship(), the API and internal complexity would increase significantly as would the potential for bugs, not to mention ambiguity in preferred methodology. > > Greetings, Torsten > > -- > DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH > Torsten Landschoff > > Office Dresden > Tel: +49-(0)351-4519587 > Fax: +49-(0)351-4519561 > > mailto:torsten.landsch...@dynamore.de > http://www.dynamore.de > > Registration court: Mannheim, HRB: 109659, based in Karlsruhe, > Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Encoding issue with SqlAlchemy + cxOracle
An update:
It seems that cx_Oracle always sends data in UTF-16 if WITH_UNICODE is
unset and unicode() objects are passed.
http://sourceforge.net/mailarchive/message.php?msg_name=AANLkTinTVdfPCn15HgXL6zo7cvRdSoE1t0_Z4eqXTGmQ%40mail.gmail.com
Regards,
Guilherme.
On Wed, Nov 17, 2010 at 2:16 PM, Michael Bayer wrote:
>
> On Nov 17, 2010, at 10:26 AM, Guilherme Menezes wrote:
>
>> Hi,
>>
>> I am having an encoding issue using Python 2.6.2, SqlAlchemy 0.6.5 and
>> cxOracle 5.0.4 to access an Oracle 11g database.
>>
>> I am using the NLS_LANG=.AL32UTF8 enviroment variable. My table in
>> Oracle is correctly configured to accept Unicode.
>>
>> First, I compiled cxOracle without the WITH_UNICODE flag (as
>> recommended by SqlAlchemy). I was passing data to SqlAlchemy as
>> unicode() objects. Everything worked without errors or warnings.
>>
>> However, sometimes Oracle would complain that the string I was trying
>> to insert into a VARCHAR2 field was too big (> 4000), even when the
>> string size ( len(the_string.encode('utf-8')) ) was about 2300 bytes
>> (before SqlAlchemy). I used a sniffer to verify that the Oracle client
>> was sending two bytes for each character (even the ASCII ones),
>> instead of sending two bytes only for special characters.
>>
>> I repeated this insertion experiment using cx_Oracle directly and
>> passing unicode() objects to it. Same thing: no errors or warnings,
>> and again the sniffer shown that the Oracle client was sending two
>> bytes per character. It seemed to me that cx_Oracle does not really
>> supports unicode() objects; it accepts them but it does not encode()
>> them to the correct encoding (as set in NLS_LANG variable).
>>
>> However, when reading data from Oracle everything worked as expected
>> (i.e. someone was converting the UTF-16(?) string to UTF-8, probably
>> Oracle itself).
>>
>> I tried two approaches to make the Oracle client send data in UTF-8
>> (not UTF-16):
>>
>> 1. Passing str() objects to SqlAlchemy. SqlAlchemy would then complain
>> in a warning:
>> * UnicodeWarning: Unicode equal comparison failed to convert both
>> arguments to Unicode - interpreting them as being unequal *
>> It worked, but it did not seem right, since I was receiving unicode()
>> objects and sending str() objetcs to SqlAlchemy. Moreover, I did not
>> know how things work inside SqlAlchemy, so I tried something else.
>>
>> 2. Compiling cx_Oracle with the WITH_UNICODE flag solved the problem
>> (utf-8 was sent to Oracle), but another warning was issued:
>> * SAWarning: cx_Oracle is compiled under Python 2.xx using the
>> WITH_UNICODE flag. Consider recompiling cx_Oracle without this flag,
>> which is in no way necessary for full support of Unicode. Otherwise,
>> all string-holding bind parameters must be explicitly typed using
>> SQLAlchemy's String type or one of its subtypes,or otherwise be passed
>> as Python unicode. Plain Python strings passed as bind parameters
>> will be silently corrupted by cx_Oracle. *
>>
>> Right now I am using cx_Oracle compiled with WITH_UNICODE, everything
>> works, but this warning is worrying me. I tried to pass a str() object
>> in UTF-8 to SqlAlchemy, and the execution halted with an encoding
>> error (UnicodeDecodeError). In other words, cx_Oracle did not silently
>> corrupted the data (at least when special characters were sent).
>>
>> I would like to know if anyone has a better (safer) solution to my
>> problem. Or am I safe enough by using the WITH_UNICODE flag and
>> passing only unicode() objects?
>
>
> Regarding the passing of utf-8 vs. utf-16 and the differences in WITH_UNICODE
> mode, you should really email the cx_oracle list about that as WITH_UNICODE
> to my knowledge only has to do with the Python interpretation of arguments,
> not its interaction with OCI. If you email their list, make sure all code
> examples are purely derived from cx_oracle, to eliminate any doubt that each
> behavior is one of cx_oracle directly. WITH_UNICODE was not intended for
> general use in Python 2.xx. if your whole application really works with it,
> then there's no reason not to use it, its just that you'll always have to
> ensure that no non-Python unicode strings ever get sent to cx_oracle which
> can be fairly tedious. It sounds like a bug that cx_oracle would be
> expanding into a two-byte-per-character stream like that.
>
> Its possible that calling setinputsizes() with cx_oracle.UNICODE may be the
> key to cx_oracle's behavior here. We call setinputsizes for every Oracle
> cursor but we currently exclude the string types from that list as it had
> some unwanted side effects.
>
> Also note that VARCHAR2(4000) is measuring itself in bytes, not characters.
> For true Unicode support Oracle provides the NVARCHAR2 type, where the
> "length" specifies the number of characters, instead of bytes. Recent
> versions of Oracle also support the form VARCHAR2(4000 CHAR) which will
> similarly measure the column in terms of characte
Re: [sqlalchemy] Something like orderinglist for secondary tables?
Hi Michael,
Thanks for your lightning fast reply!
On Thu, 2010-11-18 at 10:17 -0500, Michael Bayer wrote:
> this is correct. The functionality provided by "secondary" is that SQLA will
> maintain a table with foreign keys to the related primary keys on either
> side. It does not do anything at all with additional columns on the
> "secondary" table. If your "secondary" table has additional columns you
> need to deal with, you no longer use "secondary" and instead use the
> association object pattern :
> http://www.sqlalchemy.org/docs/orm/relationships.html#association-object .
> To make this pattern act more like "secondary" in the usual case, you use
> associationproxy:
> http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html
>
> You could most likely use the existing orderinglist extension in conjunction
> with associationproxy to maintain the ordering you want, in Python.
Okay, I updated my example code and it actually works now. However, it
feels like a lot of additional complexity just for adding order.
Greetings, Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
#! /usr/bin/python
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.orderinglist import ordering_list
from sqlalchemy.ext.associationproxy import association_proxy
meta = MetaData()
collection_table = Table("collection", meta,
Column("collection_id", Integer, primary_key=True))
item_table = Table("item", meta,
Column("item_id", Integer, primary_key=True),
Column("name", String))
collection_item_table = Table("collection_item", meta,
# Need a surrogate key to allow duplicate entries in the list.
Column("id", Integer, primary_key=True),
Column("collection_id", ForeignKey(collection_table.c.collection_id), index=True),
Column("item_id", ForeignKey(item_table.c.item_id)),
Column("item_order", Integer))
class Collection(object):
def shallow_copy(self):
new = Collection()
new.items = self.items
return new
items = association_proxy("items_relation", "item")
class Item(object):
def __init__(self, name):
self.name = name
class CollectionItemAssociation(object):
def __init__(self, item):
self.item = item
def maybe_commit(session):
session.commit()
pass
mapper(Item, item_table)
mapper(CollectionItemAssociation, collection_item_table, properties=dict(
item=relation(Item)))
mapper(Collection, collection_table, properties=dict(
items_relation=relation(
CollectionItemAssociation,
order_by=[collection_item_table.c.item_order],
collection_class=ordering_list('item_order'),)))
engine = create_engine("sqlite:///", echo=True)
Session = sessionmaker(bind=engine)
meta.create_all(engine)
items = [Item(name) for name in ("foo", "bar", "baz", "qux")]
session = Session()
c = Collection()
c.items = items[:3]
session.add(c)
maybe_commit(session)
c.items[1] = items[3]
maybe_commit(session)
assert [x.name for x in c.items] == ["foo", "qux", "baz"]
c.items.append(c.items[0])
maybe_commit(session)
assert [x.name for x in c.items] == ["foo", "qux", "baz", "foo"]
Re: [sqlalchemy] Strange behaviour with func.timediff?
On Nov 18, 2010, at 10:26 AM, Christian Démolis wrote: > > > As u can see, your wonderful library is used in many projects around the > world !) > You're the best! hey thanks for the appreciation ! > > > 2010/11/18 Michael Bayer > This has everything to do with the database driver in use and the database > itself. Acceptance of a Python timedelta object by the DBAPI and proper > interpretation of it against the result of a TIMEDIFF call (which is MySQL > specific) is not a given on most DBAPIs. For cases like these your best bet > is to first look at all SQL being emitted, and try to stick to foolproof > types like ints as far as what you pass to the database. As for SQLAlchemy, > we're just passing SQL strings and Python constructs to cursor.execute(). > > On Nov 18, 2010, at 5:04 AM, Christian Démolis wrote: > >> Hi, >> >> I found a usecase that is illogical >> >> Here is the first code and his result in term. All is False >> >> milieu = model.aliased(model.Plage) >> s2 = model.session.query( >> milieu, >> model.func.timediff(milieu.Fin, milieu.Debut), >> model.func.timediff(milieu.Fin, >> milieu.Debut)>datetime.timedelta(minutes=self.duree_cible) >> ) >> for e in s2: >> print e >> >> (Plage(589L,'L',datetime.datetime(2010, 11, 16, 8, >> 0),datetime.datetime(2010, 11, 16, 8, 10)), datetime.timedelta(0, 600), >> False) >> (Plage(590L,'L',datetime.datetime(2010, 11, 16, 14, >> 0),datetime.datetime(2010, 11, 16, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(591L,'L',datetime.datetime(2010, 11, 22, 8, >> 0),datetime.datetime(2010, 11, 22, 10, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(592L,'L',datetime.datetime(2010, 11, 22, 14, >> 0),datetime.datetime(2010, 11, 22, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(593L,'L',datetime.datetime(2010, 11, 23, 8, >> 0),datetime.datetime(2010, 11, 23, 10, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(594L,'L',datetime.datetime(2010, 11, 23, 14, >> 0),datetime.datetime(2010, 11, 23, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(595L,'L',datetime.datetime(2010, 11, 29, 8, >> 0),datetime.datetime(2010, 11, 29, 10, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(596L,'L',datetime.datetime(2010, 11, 29, 14, >> 0),datetime.datetime(2010, 11, 29, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(597L,'L',datetime.datetime(2010, 11, 30, 8, >> 0),datetime.datetime(2010, 11, 30, 10, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(598L,'L',datetime.datetime(2010, 11, 30, 14, >> 0),datetime.datetime(2010, 11, 30, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(599L,'L',datetime.datetime(2010, 12, 6, 8, 0),datetime.datetime(2010, >> 12, 6, 10, 0)), datetime.timedelta(0, 7200), False) >> (Plage(600L,'L',datetime.datetime(2010, 12, 6, 14, >> 0),datetime.datetime(2010, 12, 6, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(601L,'L',datetime.datetime(2010, 12, 7, 8, 0),datetime.datetime(2010, >> 12, 7, 10, 0)), datetime.timedelta(0, 7200), False) >> (Plage(602L,'L',datetime.datetime(2010, 12, 7, 14, >> 0),datetime.datetime(2010, 12, 7, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(603L,'L',datetime.datetime(2010, 12, 13, 8, >> 0),datetime.datetime(2010, 12, 13, 10, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(604L,'L',datetime.datetime(2010, 12, 13, 14, >> 0),datetime.datetime(2010, 12, 13, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(605L,'L',datetime.datetime(2010, 12, 14, 8, >> 0),datetime.datetime(2010, 12, 14, 10, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(606L,'L',datetime.datetime(2010, 12, 14, 14, >> 0),datetime.datetime(2010, 12, 14, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(607L,'L',datetime.datetime(2010, 12, 20, 8, >> 0),datetime.datetime(2010, 12, 20, 10, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(608L,'L',datetime.datetime(2010, 12, 20, 14, >> 0),datetime.datetime(2010, 12, 20, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(609L,'L',datetime.datetime(2010, 12, 21, 8, >> 0),datetime.datetime(2010, 12, 21, 10, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(610L,'L',datetime.datetime(2010, 12, 21, 14, >> 0),datetime.datetime(2010, 12, 21, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(611L,'L',datetime.datetime(2010, 12, 27, 8, >> 0),datetime.datetime(2010, 12, 27, 10, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(612L,'L',datetime.datetime(2010, 12, 27, 14, >> 0),datetime.datetime(2010, 12, 27, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(613L,'L',datetime.datetime(2010, 12, 28, 8, >> 0),datetime.datetime(2010, 12, 28, 10, 0)), datetime.timedelta(0, 7200), >> False) >> (Plage(614L,'L',datetime.datetime(2010, 12, 28, 14, >> 0),datetime.datetime(2010, 12, 28, 16, 0)), datetime.timedelta(0, 7200), >> False) >> (
Re: [sqlalchemy] Strange behaviour with func.timediff?
Thanks, Ok, i use MySQLdb as database driver... I solved the problem with func.unix_timestamp def lg(self): """ extrait les créneaux libres groupés """ xref = time.time() haut = model.aliased(model.Plage) milieu = model.aliased(model.Plage) bas = model.aliased(model.Plage) s = model.session.query(milieu) s = self.filtrage_requete(s, milieu) s = s.filter(milieu.EtatCourant=="L") *s = s.filter(model.func.unix_timestamp(milieu.Fin)-(self.duree_cible*60)>=model.func.unix_timestamp(milieu.Debut)) * if param.dossier_concerne.AgendaGroupement and param.dossier_concerne.AgendaSensGroupement==1: # on groupe par le haut uniquement s = s.filter(haut.Fin==milieu.Debut) s = s.filter(haut.EtatCourant=="O") if param.dossier_concerne.AgendaGroupement and param.dossier_concerne.AgendaSensGroupement==0: # on groupe par le bas uniquement s = s.filter(bas.Debut==milieu.Fin) s = s.filter(bas.EtatCourant=="O") s = s.order_by(milieu.Debut) s = s.limit(100) s = s.all() print "lg", time.time()-xref return s As u can see, your wonderful library is used in many projects around the world !) You're the best! 2010/11/18 Michael Bayer > This has everything to do with the database driver in use and the database > itself. Acceptance of a Python timedelta object by the DBAPI and proper > interpretation of it against the result of a TIMEDIFF call (which is MySQL > specific) is not a given on most DBAPIs. For cases like these your best bet > is to first look at all SQL being emitted, and try to stick to foolproof > types like ints as far as what you pass to the database. As for SQLAlchemy, > we're just passing SQL strings and Python constructs to cursor.execute(). > > On Nov 18, 2010, at 5:04 AM, Christian Démolis wrote: > > Hi, > > I found a usecase that is illogical > > Here is the first code and his result in term. All is *False* > > milieu = model.aliased(model.Plage) > s2 = model.session.query( > milieu, > model.func.timediff(milieu.Fin, milieu.Debut), > *model.func.timediff(milieu.Fin, > milieu.Debut)>datetime.timedelta(minutes=self.duree_cible)* > ) > for e in s2: > print e > > (Plage(589L,'L',datetime.datetime(2010, 11, 16, 8, > 0),datetime.datetime(2010, 11, 16, 8, 10)), datetime.timedelta(0, 600), > False) > (Plage(590L,'L',datetime.datetime(2010, 11, 16, 14, > 0),datetime.datetime(2010, 11, 16, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(591L,'L',datetime.datetime(2010, 11, 22, 8, > 0),datetime.datetime(2010, 11, 22, 10, 0)), datetime.timedelta(0, 7200), > False) > (Plage(592L,'L',datetime.datetime(2010, 11, 22, 14, > 0),datetime.datetime(2010, 11, 22, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(593L,'L',datetime.datetime(2010, 11, 23, 8, > 0),datetime.datetime(2010, 11, 23, 10, 0)), datetime.timedelta(0, 7200), > False) > (Plage(594L,'L',datetime.datetime(2010, 11, 23, 14, > 0),datetime.datetime(2010, 11, 23, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(595L,'L',datetime.datetime(2010, 11, 29, 8, > 0),datetime.datetime(2010, 11, 29, 10, 0)), datetime.timedelta(0, 7200), > False) > (Plage(596L,'L',datetime.datetime(2010, 11, 29, 14, > 0),datetime.datetime(2010, 11, 29, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(597L,'L',datetime.datetime(2010, 11, 30, 8, > 0),datetime.datetime(2010, 11, 30, 10, 0)), datetime.timedelta(0, 7200), > False) > (Plage(598L,'L',datetime.datetime(2010, 11, 30, 14, > 0),datetime.datetime(2010, 11, 30, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(599L,'L',datetime.datetime(2010, 12, 6, 8, > 0),datetime.datetime(2010, 12, 6, 10, 0)), datetime.timedelta(0, 7200), > False) > (Plage(600L,'L',datetime.datetime(2010, 12, 6, 14, > 0),datetime.datetime(2010, 12, 6, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(601L,'L',datetime.datetime(2010, 12, 7, 8, > 0),datetime.datetime(2010, 12, 7, 10, 0)), datetime.timedelta(0, 7200), > False) > (Plage(602L,'L',datetime.datetime(2010, 12, 7, 14, > 0),datetime.datetime(2010, 12, 7, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(603L,'L',datetime.datetime(2010, 12, 13, 8, > 0),datetime.datetime(2010, 12, 13, 10, 0)), datetime.timedelta(0, 7200), > False) > (Plage(604L,'L',datetime.datetime(2010, 12, 13, 14, > 0),datetime.datetime(2010, 12, 13, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(605L,'L',datetime.datetime(2010, 12, 14, 8, > 0),datetime.datetime(2010, 12, 14, 10, 0)), datetime.timedelta(0, 7200), > False) > (Plage(606L,'L',datetime.datetime(2010, 12, 14, 14, > 0),datetime.datetime(2010, 12, 14, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(607L,'L',datetime.datetime(2010, 12, 20, 8, > 0),datetime.datetime(2010, 12, 20, 10, 0)), datetime.timedelta(0, 7200), > False) > (Plage(608L,'L',datetime.datetime(2010, 12, 20, 14,
Re: [sqlalchemy] Something like orderinglist for secondary tables?
On Nov 18, 2010, at 9:32 AM, Torsten Landschoff wrote: > Hi *, > > I am fighting half a day with something I expected to be trivial: Keep > the order of items in a collection implemented vi a secondary table > (many-to-many relationship). > > Basically, I have a Collection class with a relationship to Items in the > collection. That relationship is configured via > >items=relation(Item, >secondary=collection_item_table, >order_by=[collection_item_table.c.item_order]) > > Now my problem is: How to update the item_order column in the table? So > far I did not find any way to do that. For a many-to-one relationship, > orderinglist will do this just fine. this is correct. The functionality provided by "secondary" is that SQLA will maintain a table with foreign keys to the related primary keys on either side. It does not do anything at all with additional columns on the "secondary" table. If your "secondary" table has additional columns you need to deal with, you no longer use "secondary" and instead use the association object pattern : http://www.sqlalchemy.org/docs/orm/relationships.html#association-object . To make this pattern act more like "secondary" in the usual case, you use associationproxy: http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html You could most likely use the existing orderinglist extension in conjunction with associationproxy to maintain the ordering you want, in Python. > > I tried using a MapperExtension that goes through the relevant rows in > the secondary table and updates the item_order column (code attached). > It turns out that the after_insert and after_update extension points are > called before the child collections are flushed, so the primary keys of > any new items are not available at that time. Apart from that, it is a > bit heavy on the database to update all the item rows for a selection on > each change... MapperExtension is never the right place to do anything involving relationships. SessionExtension is where you'd organize everything before or after the entire flush occurs. In this case its likely much easier to manipulate the "collection_item_table" directly before any flush happens. > > Another approach I tried was to use replace the relationship via custom > code querying the relation when reconstructing a collection instance. > The loading part works fine but I failed in populating the > collection_item_table in the first place, as the items must be flushed > before the Collection for the item_id to be available and I did not find > a way to tell SQLAlchemy of this dependency. > > Any hint on how to do this is greatly appreciated. > > Greetings, Torsten > > -- > DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH > Torsten Landschoff > > Office Dresden > Tel: +49-(0)351-4519587 > Fax: +49-(0)351-4519561 > > mailto:torsten.landsch...@dynamore.de > http://www.dynamore.de > > Registration court: Mannheim, HRB: 109659, based in Karlsruhe, > Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Strange behaviour with func.timediff?
This has everything to do with the database driver in use and the database itself. Acceptance of a Python timedelta object by the DBAPI and proper interpretation of it against the result of a TIMEDIFF call (which is MySQL specific) is not a given on most DBAPIs. For cases like these your best bet is to first look at all SQL being emitted, and try to stick to foolproof types like ints as far as what you pass to the database. As for SQLAlchemy, we're just passing SQL strings and Python constructs to cursor.execute(). On Nov 18, 2010, at 5:04 AM, Christian Démolis wrote: > Hi, > > I found a usecase that is illogical > > Here is the first code and his result in term. All is False > > milieu = model.aliased(model.Plage) > s2 = model.session.query( > milieu, > model.func.timediff(milieu.Fin, milieu.Debut), > model.func.timediff(milieu.Fin, > milieu.Debut)>datetime.timedelta(minutes=self.duree_cible) > ) > for e in s2: > print e > > (Plage(589L,'L',datetime.datetime(2010, 11, 16, 8, 0),datetime.datetime(2010, > 11, 16, 8, 10)), datetime.timedelta(0, 600), False) > (Plage(590L,'L',datetime.datetime(2010, 11, 16, 14, > 0),datetime.datetime(2010, 11, 16, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(591L,'L',datetime.datetime(2010, 11, 22, 8, 0),datetime.datetime(2010, > 11, 22, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(592L,'L',datetime.datetime(2010, 11, 22, 14, > 0),datetime.datetime(2010, 11, 22, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(593L,'L',datetime.datetime(2010, 11, 23, 8, 0),datetime.datetime(2010, > 11, 23, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(594L,'L',datetime.datetime(2010, 11, 23, 14, > 0),datetime.datetime(2010, 11, 23, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(595L,'L',datetime.datetime(2010, 11, 29, 8, 0),datetime.datetime(2010, > 11, 29, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(596L,'L',datetime.datetime(2010, 11, 29, 14, > 0),datetime.datetime(2010, 11, 29, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(597L,'L',datetime.datetime(2010, 11, 30, 8, 0),datetime.datetime(2010, > 11, 30, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(598L,'L',datetime.datetime(2010, 11, 30, 14, > 0),datetime.datetime(2010, 11, 30, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(599L,'L',datetime.datetime(2010, 12, 6, 8, 0),datetime.datetime(2010, > 12, 6, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(600L,'L',datetime.datetime(2010, 12, 6, 14, 0),datetime.datetime(2010, > 12, 6, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(601L,'L',datetime.datetime(2010, 12, 7, 8, 0),datetime.datetime(2010, > 12, 7, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(602L,'L',datetime.datetime(2010, 12, 7, 14, 0),datetime.datetime(2010, > 12, 7, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(603L,'L',datetime.datetime(2010, 12, 13, 8, 0),datetime.datetime(2010, > 12, 13, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(604L,'L',datetime.datetime(2010, 12, 13, 14, > 0),datetime.datetime(2010, 12, 13, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(605L,'L',datetime.datetime(2010, 12, 14, 8, 0),datetime.datetime(2010, > 12, 14, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(606L,'L',datetime.datetime(2010, 12, 14, 14, > 0),datetime.datetime(2010, 12, 14, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(607L,'L',datetime.datetime(2010, 12, 20, 8, 0),datetime.datetime(2010, > 12, 20, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(608L,'L',datetime.datetime(2010, 12, 20, 14, > 0),datetime.datetime(2010, 12, 20, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(609L,'L',datetime.datetime(2010, 12, 21, 8, 0),datetime.datetime(2010, > 12, 21, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(610L,'L',datetime.datetime(2010, 12, 21, 14, > 0),datetime.datetime(2010, 12, 21, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(611L,'L',datetime.datetime(2010, 12, 27, 8, 0),datetime.datetime(2010, > 12, 27, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(612L,'L',datetime.datetime(2010, 12, 27, 14, > 0),datetime.datetime(2010, 12, 27, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(613L,'L',datetime.datetime(2010, 12, 28, 8, 0),datetime.datetime(2010, > 12, 28, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(614L,'L',datetime.datetime(2010, 12, 28, 14, > 0),datetime.datetime(2010, 12, 28, 16, 0)), datetime.timedelta(0, 7200), > False) > (Plage(615L,'L',datetime.datetime(2011, 1, 3, 8, 0),datetime.datetime(2011, > 1, 3, 10, 0)), datetime.timedelta(0, 7200), False) > (Plage(616L,'L',datetime.datetime(2011, 1, 3, 14, 0),datetime.datetime(2011, > 1, 3, 16, 0)), datetime.timedelta(0, 7200), False) > (Plage(617L,'L',datetime.datetime(2011, 1, 4, 8, 0),datetime.datetime(2011, > 1, 4, 10, 0)), datetime.timedelta(0, 7200), False
[sqlalchemy] Something like orderinglist for secondary tables?
Hi *,
I am fighting half a day with something I expected to be trivial: Keep
the order of items in a collection implemented vi a secondary table
(many-to-many relationship).
Basically, I have a Collection class with a relationship to Items in the
collection. That relationship is configured via
items=relation(Item,
secondary=collection_item_table,
order_by=[collection_item_table.c.item_order])
Now my problem is: How to update the item_order column in the table? So
far I did not find any way to do that. For a many-to-one relationship,
orderinglist will do this just fine.
I tried using a MapperExtension that goes through the relevant rows in
the secondary table and updates the item_order column (code attached).
It turns out that the after_insert and after_update extension points are
called before the child collections are flushed, so the primary keys of
any new items are not available at that time. Apart from that, it is a
bit heavy on the database to update all the item rows for a selection on
each change...
Another approach I tried was to use replace the relationship via custom
code querying the relation when reconstructing a collection instance.
The loading part works fine but I failed in populating the
collection_item_table in the first place, as the items must be flushed
before the Collection for the item_id to be available and I did not find
a way to tell SQLAlchemy of this dependency.
Any hint on how to do this is greatly appreciated.
Greetings, Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
#! /usr/bin/python
from sqlalchemy import *
from sqlalchemy.orm import *
# Set up the tables
meta = MetaData()
collection_table = Table("collection", meta,
Column("collection_id", Integer, primary_key=True))
item_table = Table("item", meta,
Column("item_id", Integer, primary_key=True),
Column("name", String))
collection_item_table = Table("collection_item", meta,
Column("collection_id", ForeignKey(collection_table.c.collection_id)),
Column("item_id", ForeignKey(item_table.c.item_id)),
Column("item_order", Integer))
# Mapped classes
class Collection(object):
def shallow_copy(self):
new = Collection()
new.items = self.items
return new
class Item(object):
def __init__(self, name):
self.name = name
# In a function to test without committing (which will work of course)
def maybe_commit(session):
session.commit()
pass
class CorrectOrderExtension(MapperExtension):
"""Updates the order of the entries in the collection_item_table to match with
the order in the items field of a Collection instance.
Does not work if items were not flushed before the Collection - how to force
flushing order? I would have expected that after_update/after_insert are called
after all collection attributes are completely written out."""
def after_update(self, mapper, connection, instance):
update = collection_item_table.update().where(
collection_item_table.c.collection_id==bindparam('b_collection')).where(
collection_item_table.c.item_id==bindparam('b_item_id')
).values(item_order=bindparam('b_item_order'))
collection_id = instance.collection_id
index = 0
updates = []
for item in instance.items:
item_id = item.item_id
assert item_id
updates.append(dict(
b_collection=collection_id,
b_item_id=item_id,
b_item_order=index))
index += 1
if updates:
connection.execute(update, updates)
return EXT_CONTINUE
def after_insert(self, mapper, connection, instance):
return self.after_update(mapper, connection, instance)
# Do the Object Relational Mapping
mapper(Item, item_table)
mapper(Collection, collection_table,
extension=CorrectOrderExtension(),
properties=dict(
items=relation(Item,
secondary=collection_item_table,
order_by=[collection_item_table.c.item_order])))
# Open database
engine = create_engine("sqlite:///", echo=True)
Session = sessionmaker(bind=engine)
meta.create_all(engine)
# Some items to play with
items = [Item(name) for name in ("foo", "bar", "baz", "qux")]
session = Session()
c = Collection()
c.items = items[:3]
ses
[sqlalchemy] Re: Removing an element from an uncascaded many-to-many
On 17 Nov, 21:41, Michael Bayer wrote: > I'll also make the comment that while the pattern you're illustrating is very > unusual (cascade turned off, re-adding detached objects), the ORM is not > being consistent in its treatment of "non-included" child items in mutated > collections during flush, in that your append got flushed but the delete > doesn't - there's specific code to that effect, which is also not consistent > against one-to-many. I've added ticket 1973 which, if it proceeds, would > likely be in 0.7. Great, thanks :) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Strange behaviour with func.timediff?
Hi, I found a usecase that is illogical Here is the first code and his result in term. All is *False* milieu = model.aliased(model.Plage) s2 = model.session.query( milieu, model.func.timediff(milieu.Fin, milieu.Debut), *model.func.timediff(milieu.Fin, milieu.Debut)>datetime.timedelta(minutes=self.duree_cible)* ) for e in s2: print e (Plage(589L,'L',datetime.datetime(2010, 11, 16, 8, 0),datetime.datetime(2010, 11, 16, 8, 10)), datetime.timedelta(0, 600), False) (Plage(590L,'L',datetime.datetime(2010, 11, 16, 14, 0),datetime.datetime(2010, 11, 16, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(591L,'L',datetime.datetime(2010, 11, 22, 8, 0),datetime.datetime(2010, 11, 22, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(592L,'L',datetime.datetime(2010, 11, 22, 14, 0),datetime.datetime(2010, 11, 22, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(593L,'L',datetime.datetime(2010, 11, 23, 8, 0),datetime.datetime(2010, 11, 23, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(594L,'L',datetime.datetime(2010, 11, 23, 14, 0),datetime.datetime(2010, 11, 23, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(595L,'L',datetime.datetime(2010, 11, 29, 8, 0),datetime.datetime(2010, 11, 29, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(596L,'L',datetime.datetime(2010, 11, 29, 14, 0),datetime.datetime(2010, 11, 29, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(597L,'L',datetime.datetime(2010, 11, 30, 8, 0),datetime.datetime(2010, 11, 30, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(598L,'L',datetime.datetime(2010, 11, 30, 14, 0),datetime.datetime(2010, 11, 30, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(599L,'L',datetime.datetime(2010, 12, 6, 8, 0),datetime.datetime(2010, 12, 6, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(600L,'L',datetime.datetime(2010, 12, 6, 14, 0),datetime.datetime(2010, 12, 6, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(601L,'L',datetime.datetime(2010, 12, 7, 8, 0),datetime.datetime(2010, 12, 7, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(602L,'L',datetime.datetime(2010, 12, 7, 14, 0),datetime.datetime(2010, 12, 7, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(603L,'L',datetime.datetime(2010, 12, 13, 8, 0),datetime.datetime(2010, 12, 13, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(604L,'L',datetime.datetime(2010, 12, 13, 14, 0),datetime.datetime(2010, 12, 13, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(605L,'L',datetime.datetime(2010, 12, 14, 8, 0),datetime.datetime(2010, 12, 14, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(606L,'L',datetime.datetime(2010, 12, 14, 14, 0),datetime.datetime(2010, 12, 14, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(607L,'L',datetime.datetime(2010, 12, 20, 8, 0),datetime.datetime(2010, 12, 20, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(608L,'L',datetime.datetime(2010, 12, 20, 14, 0),datetime.datetime(2010, 12, 20, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(609L,'L',datetime.datetime(2010, 12, 21, 8, 0),datetime.datetime(2010, 12, 21, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(610L,'L',datetime.datetime(2010, 12, 21, 14, 0),datetime.datetime(2010, 12, 21, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(611L,'L',datetime.datetime(2010, 12, 27, 8, 0),datetime.datetime(2010, 12, 27, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(612L,'L',datetime.datetime(2010, 12, 27, 14, 0),datetime.datetime(2010, 12, 27, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(613L,'L',datetime.datetime(2010, 12, 28, 8, 0),datetime.datetime(2010, 12, 28, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(614L,'L',datetime.datetime(2010, 12, 28, 14, 0),datetime.datetime(2010, 12, 28, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(615L,'L',datetime.datetime(2011, 1, 3, 8, 0),datetime.datetime(2011, 1, 3, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(616L,'L',datetime.datetime(2011, 1, 3, 14, 0),datetime.datetime(2011, 1, 3, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(617L,'L',datetime.datetime(2011, 1, 4, 8, 0),datetime.datetime(2011, 1, 4, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(618L,'L',datetime.datetime(2011, 1, 4, 14, 0),datetime.datetime(2011, 1, 4, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(619L,'L',datetime.datetime(2011, 1, 10, 8, 0),datetime.datetime(2011, 1, 10, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(620L,'L',datetime.datetime(2011, 1, 10, 14, 0),datetime.datetime(2011, 1, 10, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(621L,'L',datetime.datetime(2011, 1, 11, 8, 0),datetime.datetime(2011, 1, 11, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(622L,'L',datetime.datetime(2011, 1, 11, 14, 0),datetime.datetime(2011, 1, 11, 16, 0)), datetime.timedelta(0, 7200), False) (Plage(623L,'L',datetime.datetime(2011, 1, 17, 8, 0),datetime.datetime(2011, 1, 17, 10, 0)), datetime.timedelta(0, 7200), False) (Plage(624L,'L',datetime.
Re: [sqlalchemy] TIMEDIFF and SQLAlchemy
It works!
Thx
2010/11/17 akm
> Try this:
>
> from sqlalchemy import func
> from sqlalchemy.sql import select
>
> s = select( [Plage.Id,
>func.timediff(Plage.Start, Plage.Start)
>],
> (func.timediff(Plage.Start, Plage.Start) >=
> func.time('02:20:00'))
> )
>
>
> Thanks,
> --
> Abdul Kader M
>
> On Wed, Nov 17, 2010 at 4:36 PM, Christian Démolis
> wrote:
> > Hi,
> >
> > Do you know how to do this query with sqlalchemy?
> >
> > SELECT
> > Id, TIMEDIFF( End, Start)
> > FROM
> > plage
> > WHERE
> > TIMEDIFF(End,Start)>=TIME('02:20:00');
> >
> > In my model, Start and End are DateTime
> > Start = Column('Start', DateTime)
> > End = Column('End', DateTime)
> >
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com
> .
> > For more options, visit this group at
> > http://groups.google.com/group/sqlalchemy?hl=en.
> >
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
