[sqlalchemy] Fwd: [elixir] sqlite3.OperationalError: Could not decode to UTF-8 column
Anybody knows about this? -- Forwarded message -- From: Mitch [EMAIL PROTECTED] Date: Dec 5, 2007 1:06 AM Subject: [elixir] sqlite3.OperationalError: Could not decode to UTF-8 column To: SQLElixir [EMAIL PROTECTED] Apologies in advance if this should be sent to the SQLAlchemy list instead... I have an SQLite3 database created with SQLElixir. One of the table columns, of type String, is being populated from a file which contains ISO-8859 data. When I try to query all records from the table I get a traceback which ends with: File /Library/Frameworks/Python.framework/Versions/2.5/lib/ python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/engine/ base.py, line 1497, in fetchall l = [self._process_row(self, row) for row in self._fetchall_impl()] File /Library/Frameworks/Python.framework/Versions/2.5/lib/ python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/engine/ base.py, line 1492, in _fetchall_impl return self.cursor.fetchall() sqlite3.OperationalError: Could not decode to UTF-8 column '[...]' with text [...] If I were using the sqlite3 module directly, an acceptable workaround would be to override the default text factory for the database connection, e.g. conn.text_factory = str. Is there any way to do this via the elixir or sqlalchemy APIs? Is there a better solution? Thanks for the help. -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Fwd: [elixir] sqlite3.OperationalError: Could not decode to UTF-8 column
he needs to supply data to the DB as python unicode objects..the strings from the file should be decoded first from ISO-8859. if he wants to sqlite's text_factory feature, he can use a custom connection function described in http://www.sqlalchemy.org/docs/04/dbengine.html#dbengine_establishing_custom . On Dec 5, 2007, at 3:07 AM, Gaetan de Menten wrote: Anybody knows about this? -- Forwarded message -- From: Mitch [EMAIL PROTECTED] Date: Dec 5, 2007 1:06 AM Subject: [elixir] sqlite3.OperationalError: Could not decode to UTF-8 column To: SQLElixir [EMAIL PROTECTED] Apologies in advance if this should be sent to the SQLAlchemy list instead... I have an SQLite3 database created with SQLElixir. One of the table columns, of type String, is being populated from a file which contains ISO-8859 data. When I try to query all records from the table I get a traceback which ends with: File /Library/Frameworks/Python.framework/Versions/2.5/lib/ python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/engine/ base.py, line 1497, in fetchall l = [self._process_row(self, row) for row in self._fetchall_impl()] File /Library/Frameworks/Python.framework/Versions/2.5/lib/ python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/sqlalchemy/engine/ base.py, line 1492, in _fetchall_impl return self.cursor.fetchall() sqlite3.OperationalError: Could not decode to UTF-8 column '[...]' with text [...] If I were using the sqlite3 module directly, an acceptable workaround would be to override the default text factory for the database connection, e.g. conn.text_factory = str. Is there any way to do this via the elixir or sqlalchemy APIs? Is there a better solution? Thanks for the help. -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Slow relation based assignment.
Thank you Michael - I had completely missed the backref full load.
On Dec 5, 5:27 pm, Michael Bayer [EMAIL PROTECTED] wrote:
hi martin -
the issue is that each Tag object contains a collection of 1000
employees on it, and when you make an assignment in the forwards
direction (i.e. employee.tag.append(sometag)), the corresponding
reverse relation needs to be fully loaded and then updated according
to backref semantics. since you're using eager loading by default
between employees and tags, there is a load of 20,000 rows each time
an uninitialized tags.employees collection is touched.
To prevent the backref from being unnecessarily loaded, and since it
is a large collection, you should use a dynamic collection for the
reverse:
mapper(Employee, employees, properties={
'tags': relation(Tag,
secondary=employeesTags,backref=backref('employees', lazy='dynamic'),
lazy=False)
})
mapper(Tag, tags)
the employees collection on Tag is now a filterable Query object
which only queries when read from, and you'll see that the time goes
down to nothing. you can also append and delete from a dynamic
collection like a regular list.
large collection techniques are discussed
at:http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relatio...
we do have a ticket in trac to try improving upon backrefs to not load
unloaded collections in any case, this is ticket #871.
On Dec 5, 12:07 pm, Martin Pengelly-Phillips
[EMAIL PROTECTED] wrote:
Hello again,
I have recently noticed that a particular assignment seems to be
taking a relatively long time.
Not being a database expert I am confused as to whether the last
assignment 'person.tags = tags' should be so slow when referencing
existing tags that are used by other entities - it seems to try and
get a list of all employees that use the given tag and then spends the
time doing something with the resulting set, but why?
Test case below.
If the slow assignment is expected do you have any advice on how to
speed up such a statement?
Thank you in advance,
Martin
--
import os, datetime, time
from sqlalchemy import *
from sqlalchemy.orm import *
file = '/tmp/test.db'
if os.path.isfile(file): os.remove(file)
engine = create_engine('sqlite:///%s' % file, echo=True)
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True,
transactional=False, bind=engine))
mapper = Session.mapper
# Classes
#--
class Employee(object):
def __init__(self, name=None):
self.name = name
def __repr__(self):
return '%s:%s' % (self.id, self.name)
class Tag(object):
def __init__(self, label):
self.label = label
# Setup tables
#--
employees = Table('employees', metadata,
Column('id', Integer, primary_key=True),
Column('name', String, nullable=False,
default='bob'),
Column('dob', DateTime, nullable=False,
default=datetime.datetime.now),
)
tags = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('label', String, nullable=False),
)
employeesTags = Table('employeesTags', metadata,
Column('employee_id', Integer,
ForeignKey('employees.id')),
Column('tag_id', Integer,
ForeignKey('tags.id')),
)
# Mappers
#--
mapper(Employee, employees, properties={
'tags': relation(Tag, secondary=employeesTags,
backref='employees', lazy=False)})
mapper(Tag, tags)
# Test
#--
metadata.create_all(engine)
session = Session()
session.begin()
tags = []
for i in xrange(20):
tag = Tag(str(datetime.datetime.now()))
tags.append(tag)
for i in xrange(1000):
p = Employee('john%d' % i)
p.tags = tags
session.commit()
session.clear()
session.begin()
tags = session.query(Tag).all()[:2]
person = Employee('bob')
started = time.time()
person.tags = tags
print 'Took:', time.time()-started
session.commit()
session.clear()
Session.remove()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Fwd: [elixir] sqlite3.OperationalError: Could not decode to UTF-8 column
On Dec 5, 8:00 am, Michael Bayer [EMAIL PROTECTED] wrote:
he needs to supply data to the DB as python unicode objects..the
strings from the file should be decoded first from ISO-8859.
Thanks for the help. After some experimentation I found that encoding
all string data to UTF-8, ignoring errors, did the trick:
s = unicode(s, utf-8, ignore)
In case anybody is curious, here are tests to demonstrate my problem
and some solutions. N.B. UTF-8/replace worked in these tests, but not
in my SQLElixir application.
import unittest, sqlite3
class TestCase(unittest.TestCase):
def setUp(self):
self.conn = sqlite3.connect(:memory:)
self.conn.cursor().execute('CREATE TABLE demo (value TEXT)')
def insertAndDump(self, encoding, errors, expectFailure=False):
caseName = repr([encoding, errors])
data = 'K\xf6 1366'
try:
if encoding is not None:
data = unicode(data, encoding=encoding, errors=errors)
cursor = self.conn.cursor()
cursor.execute(INSERT INTO demo (value) VALUES (?),
[data])
cursor.execute('SELECT * FROM demo')
self.failUnless(len(cursor.fetchall()) == 1)
self.failIf(expectFailure, Unexpected success for %s %
caseName)
except Exception, info:
self.failUnless(expectFailure, Failed %s: %s %
(caseName, info))
def testCannotRetrieveUnencoded(self):
self.insertAndDump(None, None, True)
def testCannotEncodeStrict(self):
self.insertAndDump(utf-8, strict, True)
def testCanRetrieve8859Strict(self):
self.insertAndDump(8859, strict)
def testCanRetrieveUTF8Ignored(self):
self.insertAndDump(utf-8, ignore)
def testCanRetrieveUTF8Replaced(self):
self.insertAndDump(utf-8, replace)
unittest.main()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Filling foreign key with mapping
Hi,
Sorry for this meaningless subject !
I am learning SQLAlchemy and I installed version 0.4.1 few days ago.
My problem is probably easy to solve. I swear I have read a good part
of the documentation, and searched this group ! But...
Short example. Two tables :
- languages having a sequence as primary key and a column of unique
short names ('en', 'fr', etc.)
- items having a foreign key on 'language_id' (see Python code
below)
When creating a new item, it is not significant to fill the foreign
key value with a integer. I wish the mapper had a way to find it
through the unique language name :
item = Item()
item.name = 'A great item'
item.language_name = 'en'
session.save(item)
Please, how to do that ?
Cheers
languages_table = Table('languages', metadata,
Column('language_id', PGInteger, Sequence('language_id_seq'),
primary_key=True),
Column('name', Unicode(), nullable=False, unique=True)
)
items_table = Table('items', metadata,
Column('item_id', PGInteger, Sequence('item_id_seq'),
primary_key=True),
Column('language_id', PGInteger,
ForeignKey('languages.language_id'), primary_key=True),
Column('name', Unicode(), nullable=False)
)
class Item(object): pass
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] order_by on related table
I have three tables a(a query of a really), b, c
a has a 1-many relationship with b
c has a 1-many relationship with b
What I would like to do is in my mapper for table c, is sort the order
of rows from b by a.name.
I don't know how to do this or if it is possible. What I have looks like:
sql_a = select([table_a], table_a.c.col1='some value').alias('a_query')
mapper(B, b_table, properties = {
'A' : relation(sql_a, lazy=False,
primaryjoin=(sql_a.c.id==table_b.c.a_id))})
mapper(C, c_table, properties = {
'Bs' : relation(B, backref='C', cascade='all, delete-orphan',
order_by=[C.A.name, c_table.c.value1,
c_table.c.value2]),
})
This gets me an AttributeError: 'InstrumentedAttribute' object has no
attribute 'name'
I have also tried:
order_by=[A.name
and
order_by=[sql_a.c.name
both get me this: ProgrammingError: (ProgrammingError) missing
FROM-clause entry for table sql_a.name
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.4.1 example vertical.py not working
it appears to have broken since the session adjustments in 0.4.1. it should work in 0.4.0 for now. On Dec 5, 2:05 pm, paftek [EMAIL PROTECTED] wrote: Using Python 2.5.1, I can not get this example to work :http://svn.sqlalchemy.org/sqlalchemy/tags/rel_0_4_1/examples/vertical... It crashes with : Traceback (most recent call last): File vertical.py, line 161, in module session.save(entity3) ... sqlalchemy.exceptions.InvalidRequestError: Instance '[EMAIL PROTECTED]' is with key (class '__main__.EntityField', (1,), None) already persisted with a different identity I do not know why. Any clue ? Thanks ! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] SQLAlchemy 0.4.1 example vertical.py not working
Using Python 2.5.1, I can not get this example to work : http://svn.sqlalchemy.org/sqlalchemy/tags/rel_0_4_1/examples/vertical/vertical.py It crashes with : Traceback (most recent call last): File vertical.py, line 161, in module session.save(entity3) ... sqlalchemy.exceptions.InvalidRequestError: Instance '[EMAIL PROTECTED]' is with key (class '__main__.EntityField', (1,), None) already persisted with a different identity I do not know why. Any clue ? Thanks ! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Filling foreign key with mapping
I believe what you need to do is get an instance of a language object
something like:
item.Language = session.query(Language).filter_by(name='en').first()
There maybe a better way to do this, but its how I currently do it (so
if there is a better way I would like to know).
paftek wrote:
Hi,
Sorry for this meaningless subject !
I am learning SQLAlchemy and I installed version 0.4.1 few days ago.
My problem is probably easy to solve. I swear I have read a good part
of the documentation, and searched this group ! But...
Short example. Two tables :
- languages having a sequence as primary key and a column of unique
short names ('en', 'fr', etc.)
- items having a foreign key on 'language_id' (see Python code
below)
When creating a new item, it is not significant to fill the foreign
key value with a integer. I wish the mapper had a way to find it
through the unique language name :
item = Item()
item.name = 'A great item'
item.language_name = 'en'
session.save(item)
Please, how to do that ?
Cheers
languages_table = Table('languages', metadata,
Column('language_id', PGInteger, Sequence('language_id_seq'),
primary_key=True),
Column('name', Unicode(), nullable=False, unique=True)
)
items_table = Table('items', metadata,
Column('item_id', PGInteger, Sequence('item_id_seq'),
primary_key=True),
Column('language_id', PGInteger,
ForeignKey('languages.language_id'), primary_key=True),
Column('name', Unicode(), nullable=False)
)
class Item(object): pass
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] distinct entries when mapping many-to-many
I am mapping 3 tables linearly. The first 2 have a many-to-many relation and the last one is a one-to-one relation. I would like to get all the distinct entries from the third table. I was not able to figure how to do the mapping. So, I tried by doing my own set of entries outside of SA but when I am trying to retreive the infos from the third table using in_, my query is too long to be executed. How is it possible to do the kind of mapping I would like? thank you very much, Mathieu --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Slow relation based assignment.
hi martin -
the issue is that each Tag object contains a collection of 1000
employees on it, and when you make an assignment in the forwards
direction (i.e. employee.tag.append(sometag)), the corresponding
reverse relation needs to be fully loaded and then updated according
to backref semantics. since you're using eager loading by default
between employees and tags, there is a load of 20,000 rows each time
an uninitialized tags.employees collection is touched.
To prevent the backref from being unnecessarily loaded, and since it
is a large collection, you should use a dynamic collection for the
reverse:
mapper(Employee, employees, properties={
'tags': relation(Tag,
secondary=employeesTags,backref=backref('employees', lazy='dynamic'),
lazy=False)
})
mapper(Tag, tags)
the employees collection on Tag is now a filterable Query object
which only queries when read from, and you'll see that the time goes
down to nothing. you can also append and delete from a dynamic
collection like a regular list.
large collection techniques are discussed at:
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_largecollections
we do have a ticket in trac to try improving upon backrefs to not load
unloaded collections in any case, this is ticket #871.
On Dec 5, 12:07 pm, Martin Pengelly-Phillips
[EMAIL PROTECTED] wrote:
Hello again,
I have recently noticed that a particular assignment seems to be
taking a relatively long time.
Not being a database expert I am confused as to whether the last
assignment 'person.tags = tags' should be so slow when referencing
existing tags that are used by other entities - it seems to try and
get a list of all employees that use the given tag and then spends the
time doing something with the resulting set, but why?
Test case below.
If the slow assignment is expected do you have any advice on how to
speed up such a statement?
Thank you in advance,
Martin
--
import os, datetime, time
from sqlalchemy import *
from sqlalchemy.orm import *
file = '/tmp/test.db'
if os.path.isfile(file): os.remove(file)
engine = create_engine('sqlite:///%s' % file, echo=True)
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True,
transactional=False, bind=engine))
mapper = Session.mapper
# Classes
#--
class Employee(object):
def __init__(self, name=None):
self.name = name
def __repr__(self):
return '%s:%s' % (self.id, self.name)
class Tag(object):
def __init__(self, label):
self.label = label
# Setup tables
#--
employees = Table('employees', metadata,
Column('id', Integer, primary_key=True),
Column('name', String, nullable=False,
default='bob'),
Column('dob', DateTime, nullable=False,
default=datetime.datetime.now),
)
tags = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('label', String, nullable=False),
)
employeesTags = Table('employeesTags', metadata,
Column('employee_id', Integer,
ForeignKey('employees.id')),
Column('tag_id', Integer,
ForeignKey('tags.id')),
)
# Mappers
#--
mapper(Employee, employees, properties={
'tags': relation(Tag, secondary=employeesTags,
backref='employees', lazy=False)})
mapper(Tag, tags)
# Test
#--
metadata.create_all(engine)
session = Session()
session.begin()
tags = []
for i in xrange(20):
tag = Tag(str(datetime.datetime.now()))
tags.append(tag)
for i in xrange(1000):
p = Employee('john%d' % i)
p.tags = tags
session.commit()
session.clear()
session.begin()
tags = session.query(Tag).all()[:2]
person = Employee('bob')
started = time.time()
person.tags = tags
print 'Took:', time.time()-started
session.commit()
session.clear()
Session.remove()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by on related table
On Dec 5, 2007, at 1:56 PM, David Gardner wrote:
I have three tables a(a query of a really), b, c
a has a 1-many relationship with b
c has a 1-many relationship with b
What I would like to do is in my mapper for table c, is sort the order
of rows from b by a.name.
I don't know how to do this or if it is possible. What I have looks
like:
sql_a = select([table_a], table_a.c.col1='some
value').alias('a_query')
mapper(B, b_table, properties = {
'A' : relation(sql_a, lazy=False,
primaryjoin=(sql_a.c.id==table_b.c.a_id))})
mapper(C, c_table, properties = {
'Bs' : relation(B, backref='C', cascade='all, delete-orphan',
order_by=[C.A.name, c_table.c.value1,
c_table.c.value2]),
})
This gets me an AttributeError: 'InstrumentedAttribute' object has no
attribute 'name'
this error is becuase C.A is not a gateway to the columns on the A
mapper.
I have also tried:
order_by=[A.name
and
order_by=[sql_a.c.name
both get me this: ProgrammingError: (ProgrammingError) missing
FROM-clause entry for table sql_a.name
this error is because the relation from C-B has no relationship to
As table at all, and it is not used in the generated query. to have
As table be a part of it in all cases, youd have to map A's column
onto B's mapper, which is fairly messy:
mapper(B, select([b_table, a.c.name], b_table.c.a_id==a.c.id))
you can try creating this mapper as a non-primary mapper and using
it for just the C-B relation:
s = select([b_table, a.c.name], b_table.c.a_id==a.c.id)
mapper(C, ctable, properties={
'bs':relation(mapper(B, s, non_primary=True), order_by=[s.c.name])
})
I havent tried the above myself so see if it works for you.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Slow relation based assignment.
Hello again,
I have recently noticed that a particular assignment seems to be
taking a relatively long time.
Not being a database expert I am confused as to whether the last
assignment 'person.tags = tags' should be so slow when referencing
existing tags that are used by other entities - it seems to try and
get a list of all employees that use the given tag and then spends the
time doing something with the resulting set, but why?
Test case below.
If the slow assignment is expected do you have any advice on how to
speed up such a statement?
Thank you in advance,
Martin
--
import os, datetime, time
from sqlalchemy import *
from sqlalchemy.orm import *
file = '/tmp/test.db'
if os.path.isfile(file): os.remove(file)
engine = create_engine('sqlite:///%s' % file, echo=True)
metadata = MetaData()
Session = scoped_session(sessionmaker(autoflush=True,
transactional=False, bind=engine))
mapper = Session.mapper
# Classes
#--
class Employee(object):
def __init__(self, name=None):
self.name = name
def __repr__(self):
return '%s:%s' % (self.id, self.name)
class Tag(object):
def __init__(self, label):
self.label = label
# Setup tables
#--
employees = Table('employees', metadata,
Column('id', Integer, primary_key=True),
Column('name', String, nullable=False,
default='bob'),
Column('dob', DateTime, nullable=False,
default=datetime.datetime.now),
)
tags = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('label', String, nullable=False),
)
employeesTags = Table('employeesTags', metadata,
Column('employee_id', Integer,
ForeignKey('employees.id')),
Column('tag_id', Integer,
ForeignKey('tags.id')),
)
# Mappers
#--
mapper(Employee, employees, properties={
'tags': relation(Tag, secondary=employeesTags,
backref='employees', lazy=False)
})
mapper(Tag, tags)
# Test
#--
metadata.create_all(engine)
session = Session()
session.begin()
tags = []
for i in xrange(20):
tag = Tag(str(datetime.datetime.now()))
tags.append(tag)
for i in xrange(1000):
p = Employee('john%d' % i)
p.tags = tags
session.commit()
session.clear()
session.begin()
tags = session.query(Tag).all()[:2]
person = Employee('bob')
started = time.time()
person.tags = tags
print 'Took:', time.time()-started
session.commit()
session.clear()
Session.remove()
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] delete children of object w/o delete of object?
with sqlalchemy 0.4.1, Is there an idiom for delete the children of the object without actually deleting the object itself? I tried session.delete (obj) session.flush() # add new children session.save (obj) session.flush() But it gave me the error InvalidRequestError: Instance '[EMAIL PROTECTED]' is already persistent which does not appear correct either. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.4.1 example vertical.py not working
the example is repaired in rev 3856 of the SVN trunk. it just uses a contextual session now. On Dec 5, 2007, at 2:05 PM, paftek wrote: Using Python 2.5.1, I can not get this example to work : http://svn.sqlalchemy.org/sqlalchemy/tags/rel_0_4_1/examples/vertical/vertical.py It crashes with : Traceback (most recent call last): File vertical.py, line 161, in module session.save(entity3) ... sqlalchemy.exceptions.InvalidRequestError: Instance '[EMAIL PROTECTED]' is with key (class '__main__.EntityField', (1,), None) already persisted with a different identity I do not know why. Any clue ? Thanks ! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by on related table
Michael thanks for the help, this is how I was able to get it working.
Probably isn't the most efficient, but it works, I couldn't implement it
the way you proposed because I still need to be able to do a_row = b_row.A
-
sql_b = select([b_table, sql_a.c.name], b_table.c.a_id =
sql_a.c.id).alias('b_query')
mapper(b, sql_b, properties = {
'A' : relation(A, lazy=False, primaryjoin=(sql_a.c.id==sql_b.c.a_id))})
mapper(C, c_table, properties = {
'Bs' : relation(B, backref='C', cascade='all, delete-orphan',
order_by=[sql_b.c.name, sql_c.c.value1,
sql_c.c.value2]),
})
Michael Bayer wrote:
On Dec 5, 2007, at 1:56 PM, David Gardner wrote:
I have three tables a(a query of a really), b, c
a has a 1-many relationship with b
c has a 1-many relationship with b
What I would like to do is in my mapper for table c, is sort the order
of rows from b by a.name.
I don't know how to do this or if it is possible. What I have looks
like:
sql_a = select([table_a], table_a.c.col1='some
value').alias('a_query')
mapper(B, b_table, properties = {
'A' : relation(sql_a, lazy=False,
primaryjoin=(sql_a.c.id==table_b.c.a_id))})
mapper(C, c_table, properties = {
'Bs' : relation(B, backref='C', cascade='all, delete-orphan',
order_by=[C.A.name, c_table.c.value1,
c_table.c.value2]),
})
This gets me an AttributeError: 'InstrumentedAttribute' object has no
attribute 'name'
this error is becuase C.A is not a gateway to the columns on the A
mapper.
I have also tried:
order_by=[A.name
and
order_by=[sql_a.c.name
both get me this: ProgrammingError: (ProgrammingError) missing
FROM-clause entry for table sql_a.name
this error is because the relation from C-B has no relationship to
As table at all, and it is not used in the generated query. to have
As table be a part of it in all cases, youd have to map A's column
onto B's mapper, which is fairly messy:
mapper(B, select([b_table, a.c.name], b_table.c.a_id==a.c.id))
you can try creating this mapper as a non-primary mapper and using
it for just the C-B relation:
s = select([b_table, a.c.name], b_table.c.a_id==a.c.id)
mapper(C, ctable, properties={
'bs':relation(mapper(B, s, non_primary=True), order_by=[s.c.name])
})
I havent tried the above myself so see if it works for you.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
