Re: [sqlalchemy] sqla and firebird
On 09/11/2010 01:34, Michael Bayer wrote: not sure if anyone knows. I have 2.1 running here for my own tests. On Nov 8, 2010, at 5:05 PM, Domingo Aguilera wrote: Is firebird 2.5 working with sqla. ? I am in the midst of moving to FB 2.5 and SA 0.6.5 and have not encountered any problems yet. You are on kinterbasdb 3.3.0? If not you can get it from here: http://www.firebirdsql.org/index.php?op=develsub=python Werner -- 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] Re: 'list' object has no attribute '_state'
Hi, Michael
Thanks so much for your feedback. You were right on the button (second
scenario): I was pretty sure I was passing a scalar to the list's
append() method but was, in fact, passing it another list.
Help greatly appreciated, really.
Regards
On Nov 6, 2:58 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Nov 6, 2010, at 1:32 AM, pianoman wrote:
Hi
I feel I have exhausted all the usual options in finding a solution
for this problem.
I'm using Python 2.5.5, SQLAlchemy 0.4.6, and Elixir 0.5.2. I'm really
not sure how much code I should include for this to make any sense to
others.
--
The actual traceback is as follows:
ERROR: An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line statement', (30, 0))
---
AttributeError Traceback (most recent call
last)
C:\Users\User\My iFolder\My Development\Python\TurboGears\DMS-Test
\dmstest\model.py in module()
1
2
3
4
5
C:\Users\User\My iFolder\My Development\Python\TurboGears\DMS-Test
\dmstest\model.py in register(cls, person, ref, versio
n, file_name, reg_date, write)
564 names.append(record['author-%s' % a])
565 else:
-- 566 doc.authors.append(author)
567 a += 1
568
c:\VirtualEnvs\tg1env\lib\site-packages\sqlalchemy-0.4.6-py2.5.egg
\sqlalchemy\orm\collections.pyc in append(self, item,
_sa_initiator)
919 if executor:
920
executor.attr.fire_append_event(executor.owner_state,
-- 921 item,
_sa_initiator)
922 fn(self, item)
923 _tidy(append)
c:\VirtualEnvs\tg1env\lib\site-packages\sqlalchemy-0.4.6-py2.5.egg
\sqlalchemy\orm\attributes.pyc in fire_append_event(se
lf, state, value, initiator)
509
510 if self.trackparent and value is not None:
-- 511 self.sethasparent(value._state, True)
512 instance = state.obj()
513 for ext in self.extensions:
AttributeError: 'list' object has no attribute '_state'
the kind of thing that would do this is:
someobject.some_scalar_attribute = [some list]
or:
someobject.some_related_list.append([some list])
So your stack trace above would suggest that author is a Python list when
it should be a scalar element. Using pdb would help you to drop into a
console at the point the error occurs in which you can inspect author as
well as travel up the stack to see where it came from.
--
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] FlushError: Instance has NULL identity
Hi,
I need to use dynamic table names in a project (though for the same
object). Now I got the following problem:
- I fetch data from an RSS feed to fill my database. Its an RSS feed
with many pages, each page has around 100 items
- I fetch the first page and create a table + mapper for a dynamic id
(called my_id below)
- then I do session.add and session.commit for each item in the feed,
everything ok so far
- after I fetch the second page (my_id is the same), I attempt to
recreate table and mapper (see code below) or simply readd them if
they already exist, that seems working as well
- I do the same for each item in the feed as the first time, but this
time it raise the following exception:
sqlalchemy.orm.exc.FlushError: Instance MyModel at 0x1bc1590 has a
NULL identity key. Check if this flush is occuring at an
inappropriate time, such as during a load operation.
Here my data model (simplified) and the code to simply save a new item
from the feed:
===
import sqlalchemy
from sqlalchemy import import orm
mymetadata = sqlalchemy.MetaData()
class MyModel(object):
def __init__(self, **kwargs):
title = kwargs.get('title')
body = kwargs.get('body')
class MyModelTable(object):
def __init__(self, my_id):
metadata = mymetadata
table_name = 'mymodel_%s' % my_id
self.table = sqlalchemy.Table(table_name, metadata,
sqlalchemy.Column('id', sqlalchemy.Integer,
primary_key=True),
sqlalchemy.Column('title', sqlalchemy.String(255),
index=True),
sqlalchemy.Column('body', sqlalchemy.Text),
useexisiting=True)
self.table.create(get_engine(), checkfirst=True) #get_engine()
is helper function to retrieve the engine (not listed here)
orm.mapper(MyModel, self.table)
def save_items(my_id, items): #assuming items is already parsed and
saved in a list
model_table = MyModelTable(my_id)
session = orm.sessionmaker(bind=get_engine(), autocommit=False,
autoflush=True)
for item in items:
item_data = dict(title=item['title'],
body=item['description'])
new_model_instance = MyModel(**item_data)
session.add(new_model_instance)
session.commit()
session.close()
orm.clear_mappers() #to avoid the exception when mapping the same
object to anoher ot the same table
===
Here is the whole traceback after the commit:
===
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 623, in commit
self.transaction.commit()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 385, in commit
self._prepare_impl()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 369, in _prepare_impl
self.session.flush()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 1397, in flush
self._flush(objects)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 1487, in _flush
flush_context.finalize_flush_changes()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/unitofwork.py, line 320, in
finalize_flush_changes
self.session._register_newly_persistent(state)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 1056, in
_register_newly_persistent
'operation.' % mapperutil.state_str(state))
sqlalchemy.orm.exc.FlushError: Instance MyModel at 0x1bc1590 has a
NULL identity key. Check if this flush is occuring at an
inappropriate time, such as during a load operation.
===
save_items() is called each time a new page is fetched (either for the
same my_id) or another one.
This FlushError only happens when I have more than one page for the
same my_id and they are saved one after another. I also never
happens for the first page, neither for different my_id's when
staying with one page.
According to the release notes of SQLAlchemy 0.6.5 this exception
raises when
===
Added an assertion during flush which ensures
that no NULL-holding identity keys were generated
on newly persistent objects.
This can occur when user defined code inadvertently
triggers flushes on not-fully-loaded objects.
===
Though I don't see why it happens in my example. I appreaciate any
help. 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] FlushError (Instance has a NULL identity key) for dynamicaly created tables
Hi,
I need to use dynamic table names in a project (though for the same
object). Now I got the following problem:
- I fetch data from an RSS feed to fill my database. Its an RSS feed
with many pages, each page has around 100 items
- I fetch the first page and create a table + mapper for a dynamic id
(called my_id below)
- then I do session.add and session.commit for each item in the feed,
everything ok so far
- after I fetch the second page (my_id is the same), I attempt to
recreate table and mapper (see code below) or simply readd them if
they already exist, that seems working as well
- I do the same for each item in the feed as the first time, but this
time it raise the following exception:
sqlalchemy.orm.exc.FlushError: Instance MyModel at 0x1bc1590 has a
NULL identity key. Check if this flush is occuring at an
inappropriate time, such as during a load operation.
Here my data model (simplified) and the code to simply save a new item
from the feed:
=
import sqlalchemy
from sqlalchemy import import orm
mymetadata = sqlalchemy.MetaData()
class MyModel(object):
def __init__(self, **kwargs):
title = kwargs.get('title')
body = kwargs.get('body')
class MyModelTable(object):
def __init__(self, my_id):
metadata = mymetadata
table_name = 'mymodel_%s' % my_id
self.table = sqlalchemy.Table(table_name, metadata,
sqlalchemy.Column('id', sqlalchemy.Integer,
primary_key=True),
sqlalchemy.Column('title', sqlalchemy.String(255),
index=True),
sqlalchemy.Column('body', sqlalchemy.Text),
useexisiting=True)
self.table.create(get_engine(), checkfirst=True) #get_engine()
is helper function to retrieve the engine (not listed here)
orm.mapper(MyModel, self.table)
def save_items(my_id, items): #assuming items is already parsed and
saved in a list
model_table = MyModelTable(my_id)
session = orm.sessionmaker(bind=get_engine(), autocommit=False,
autoflush=True)
for item in items:
item_data = dict(title=item['title'],
body=item['description'])
new_model_instance = MyModel(**item_data)
session.add(new_model_instance)
session.commit()
session.close()
orm.clear_mappers() #to avoid the exception when mapping the same
object to anoher ot the same table
=
Here is the whole traceback after the commit:
=
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 623, in commit
self.transaction.commit()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 385, in commit
self._prepare_impl()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 369, in _prepare_impl
self.session.flush()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 1397, in flush
self._flush(objects)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 1487, in _flush
flush_context.finalize_flush_changes()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/unitofwork.py, line 320, in
finalize_flush_changes
self.session._register_newly_persistent(state)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 1056, in
_register_newly_persistent
'operation.' % mapperutil.state_str(state))
sqlalchemy.orm.exc.FlushError: Instance MyModel at 0x1bc1590 has a
NULL identity key. Check if this flush is occuring at an
inappropriate time, such as during a load operation.
=
save_items() is called each time a new page is fetched (either for the
same my_id) or another one.
This FlushError only happens when I have more than one page for the
same my_id and they are saved one after another. I also never
happens for the first page, neither for different my_id's when
staying with one page.
According to the release notes of SQLAlchemy 0.6.5 this exception
raises when
=
Added an assertion during flush which ensures
that no NULL-holding identity keys were generated
on newly persistent objects.
This can occur when user defined code inadvertently
triggers flushes on not-fully-loaded objects.
=
Though I don't see why it happens in my example. If I commit the
primary_key should be auto-generated.
My first guess is that something is wrong about how I (re)generate
Table and mapper.
I appreaciate any help. 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] Re: FlushError: Instance has NULL identity
Sorry for the double thread. I was waiting for it to show up for
hours. Please remove one of them. Thanks
On Nov 9, 4:44 pm, Torsten Engelbrecht
torsten.engelbre...@googlemail.com wrote:
Hi,
I need to use dynamic table names in a project (though for the same
object). Now I got the following problem:
- I fetch data from an RSS feed to fill my database. Its an RSS feed
with many pages, each page has around 100 items
- I fetch the first page and create a table + mapper for a dynamic id
(called my_id below)
- then I do session.add and session.commit for each item in the feed,
everything ok so far
- after I fetch the second page (my_id is the same), I attempt to
recreate table and mapper (see code below) or simply readd them if
they already exist, that seems working as well
- I do the same for each item in the feed as the first time, but this
time it raise the following exception:
sqlalchemy.orm.exc.FlushError: Instance MyModel at 0x1bc1590 has a
NULL identity key. Check if this flush is occuring at an
inappropriate time, such as during a load operation.
Here my data model (simplified) and the code to simply save a new item
from the feed:
===
import sqlalchemy
from sqlalchemy import import orm
mymetadata = sqlalchemy.MetaData()
class MyModel(object):
def __init__(self, **kwargs):
title = kwargs.get('title')
body = kwargs.get('body')
class MyModelTable(object):
def __init__(self, my_id):
metadata = mymetadata
table_name = 'mymodel_%s' % my_id
self.table = sqlalchemy.Table(table_name, metadata,
sqlalchemy.Column('id', sqlalchemy.Integer,
primary_key=True),
sqlalchemy.Column('title', sqlalchemy.String(255),
index=True),
sqlalchemy.Column('body', sqlalchemy.Text),
useexisiting=True)
self.table.create(get_engine(), checkfirst=True) #get_engine()
is helper function to retrieve the engine (not listed here)
orm.mapper(MyModel, self.table)
def save_items(my_id, items): #assuming items is already parsed and
saved in a list
model_table = MyModelTable(my_id)
session = orm.sessionmaker(bind=get_engine(), autocommit=False,
autoflush=True)
for item in items:
item_data = dict(title=item['title'],
body=item['description'])
new_model_instance = MyModel(**item_data)
session.add(new_model_instance)
session.commit()
session.close()
orm.clear_mappers() #to avoid the exception when mapping the same
object to anoher ot the same table
===
Here is the whole traceback after the commit:
===
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 623, in commit
self.transaction.commit()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 385, in commit
self._prepare_impl()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 369, in _prepare_impl
self.session.flush()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 1397, in flush
self._flush(objects)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 1487, in _flush
flush_context.finalize_flush_changes()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/unitofwork.py, line 320, in
finalize_flush_changes
self.session._register_newly_persistent(state)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.5-
py2.6.egg/sqlalchemy/orm/session.py, line 1056, in
_register_newly_persistent
'operation.' % mapperutil.state_str(state))
sqlalchemy.orm.exc.FlushError: Instance MyModel at 0x1bc1590 has a
NULL identity key. Check if this flush is occuring at an
inappropriate time, such as during a load operation.
===
save_items() is called each time a new page is fetched (either for the
same my_id) or another one.
This FlushError only happens when I have more than one page for the
same my_id and they are saved one after another. I also never
happens for the first page, neither for different my_id's when
staying with one page.
According to the release notes of SQLAlchemy 0.6.5 this exception
raises when
===
Added an assertion during flush which ensures
that no NULL-holding identity keys were generated
on newly persistent objects.
This can occur when user defined code inadvertently
triggers flushes on not-fully-loaded objects.
===
Though I don't see why it happens in my example. I appreaciate any
help. 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
[sqlalchemy] Re: sqla and firebird
Werner, I am using kinterbasdb downloaded just few days ago. Also I am using sqla 0.6.5 . I worked with firebird rdbms engine several years ago but not from python. This is the first time I am using firebird from python and from sqlalchemy ( been using python since 2004 ). I have an app in which every table is created with declarative_base and in every engine I've tried, it was enough to write... id = Column( Integer, primary_key = True ) This caused every engine to use whatever is needed to have an autoincrement field. In the case of firebird+kinterbasdb it was necessary to add the Sequence in the argument of Column as ... id = Column( Integer, Sequence(whatever, optional = True ), primary_key = True ) On Nov 9, 2:03 am, werner wbru...@free.fr wrote: On 09/11/2010 01:34, Michael Bayer wrote: not sure if anyone knows. I have 2.1 running here for my own tests. On Nov 8, 2010, at 5:05 PM, Domingo Aguilera wrote: Is firebird 2.5 working with sqla. ? I am in the midst of moving to FB 2.5 and SA 0.6.5 and have not encountered any problems yet. You are on kinterbasdb 3.3.0? If not you can get it from here:http://www.firebirdsql.org/index.php?op=devel⊂=python Werner -- 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] Does SQLAlchemy support read-write splitting?
this is usually called a read slave and you do it by either switching the bind used in your Session, or by using two different sessions.In a web application I'll typically build a decorator for controllers that performs this switch transparently: @uses_master def my_controller_method(self): Session.add(SomeObject()) Session.commit() return success! On Nov 8, 2010, at 9:52 PM, Li-Wen Hsu wrote: Hello, Does SQLAlchemy support read-write splitting? It seems not mentioned in the document. Or does it is not intended to support in ORM layer? This is somehow important for scaling , and could be convenient if we can achieve this in SQLAlchemy. Thanks, Li-Wen -- 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] FlushError: Instance has NULL identity
On Nov 9, 2010, at 3:44 AM, Torsten Engelbrecht wrote:
Hi,
I need to use dynamic table names in a project (though for the same
object). Now I got the following problem:
- I fetch data from an RSS feed to fill my database. Its an RSS feed
with many pages, each page has around 100 items
- I fetch the first page and create a table + mapper for a dynamic id
(called my_id below)
- then I do session.add and session.commit for each item in the feed,
everything ok so far
- after I fetch the second page (my_id is the same), I attempt to
recreate table and mapper (see code below) or simply readd them if
they already exist, that seems working as well
- I do the same for each item in the feed as the first time, but this
time it raise the following exception:
sqlalchemy.orm.exc.FlushError: Instance MyModel at 0x1bc1590 has a
NULL identity key. Check if this flush is occuring at an
inappropriate time, such as during a load operation.
Here my data model (simplified) and the code to simply save a new item
from the feed:
===
import sqlalchemy
from sqlalchemy import import orm
mymetadata = sqlalchemy.MetaData()
class MyModel(object):
def __init__(self, **kwargs):
title = kwargs.get('title')
body = kwargs.get('body')
class MyModelTable(object):
def __init__(self, my_id):
metadata = mymetadata
table_name = 'mymodel_%s' % my_id
self.table = sqlalchemy.Table(table_name, metadata,
sqlalchemy.Column('id', sqlalchemy.Integer,
primary_key=True),
sqlalchemy.Column('title', sqlalchemy.String(255),
index=True),
sqlalchemy.Column('body', sqlalchemy.Text),
useexisiting=True)
self.table.create(get_engine(), checkfirst=True) #get_engine()
is helper function to retrieve the engine (not listed here)
orm.mapper(MyModel, self.table)
def save_items(my_id, items): #assuming items is already parsed and
saved in a list
model_table = MyModelTable(my_id)
session = orm.sessionmaker(bind=get_engine(), autocommit=False,
autoflush=True)
for item in items:
item_data = dict(title=item['title'],
body=item['description'])
new_model_instance = MyModel(**item_data)
session.add(new_model_instance)
session.commit()
session.close()
orm.clear_mappers() #to avoid the exception when mapping the same
object to anoher ot the same table
===
save_items() is called each time a new page is fetched (either for the
same my_id) or another one.
This FlushError only happens when I have more than one page for the
same my_id and they are saved one after another. I also never
happens for the first page, neither for different my_id's when
staying with one page.
According to the release notes of SQLAlchemy 0.6.5 this exception
raises when
===
Added an assertion during flush which ensures
that no NULL-holding identity keys were generated
on newly persistent objects.
This can occur when user defined code inadvertently
triggers flushes on not-fully-loaded objects.
===
Though I don't see why it happens in my example. I appreaciate any
help. Thanks
While the above code uses some patterns that are unnecessary, there's nothing
about it which would cause that error to occur. To my knowledge, the only way
that particular condition arises is if a flush occurs at an inappropriate time,
namely within an object load, which does not seem to be the case based on your
stack trace.So I am extremely curious how you're getting that to occur.
The advised way to do entity name, which is roughly the pattern you have
above, that is, one which maps the same class to multiple tables, is to create
new classes for each table. These can be ad-hoc subclasses of MyModel if the
base class has other significant behaviors. A description of this is at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName .You'd probably
want to pull your mapped classes from a dictionary, and if not present *then*
create the Table object and mapper - the regeneration of the Table above with
useexisting is not an optimal way to do things.
--
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] no ALTER statements issued when using use_alter=True
The following simple code does not seem to produce the ALTER statement
expected for the Contact.ownername column.
When I look at the DB, there is no ForeignKeyConstraint on
Contact.ownername.
I think this results in problems later on: when I activate the
relationships, I cannot do
v=User(username=v)
session.add(v)
session.commit()
c2=Contact(contactname=c2,owner=v)
session.add(c2)
session.commit()
sqlalchemy.exc.IntegrityError: (IntegrityError) contact.ownername may
not be NULL u'INSERT INTO contact (contactname, ownername) VALUES
(?, ?)' ('c2', None)
which doesn't seem normal.
Here is the code:
from datetime import datetime
from sqlalchemy import (create_engine, MetaData, Table, Column,
Integer,
String, DateTime, Float, ForeignKey, and_, select)
from sqlalchemy.orm import mapper, relationship, create_session,
column_property,sessionmaker
from sqlalchemy.ext.associationproxy import AssociationProxy
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
#engine = create_engine('sqlite:///test.db', echo=True)
metadata = MetaData(engine)
Session = sessionmaker(bind=engine)
session=Session()
Base = declarative_base(bind=engine, metadata=metadata)
class User(Base):
__tablename__ = 'user'
username = Column(String(50), primary_key=True)
contactname = Column(String(50),
ForeignKey('contact.contactname'), nullable=True)
class Contact(Base):
__tablename__ = 'contact'
contactname = Column(String(50), primary_key=True)
ownername = Column(String(50), ForeignKey('user.username',
use_alter=True, name=fk_owner_constraint), nullable=False)
#contacted_users = relationship(User, backref=contact,
post_update=True, primaryjoin=contactname==User.contactname)
#owner = relationship(User, backref=owned_contacts,
post_update=True, primaryjoin=User.username==ownername)
metadata.create_all(engine)
--
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] no ALTER statements issued when using use_alter=True
On Nov 9, 2010, at 8:47 AM, yannick wrote:
The following simple code does not seem to produce the ALTER statement
expected for the Contact.ownername column.
When I look at the DB, there is no ForeignKeyConstraint on
Contact.ownername.
SQLite doesn't support ALTER on an existing Column construct. use_alter=True
is a noop when using SQLite.This was definitely written out somewhere at
some point but doesn't appear to be in current documentation.
The error message you are receiving is due to the missing value for the
ownername column when the INSERT for Contact proceeds. post_update=True
means a second UPDATE statement will be emitted to populate this column, so the
column here cannot be nullable with that setting; however post_update=True is
not needed for that relationship, its only needed on one of the mutually
dependent relationships.
SQLite does not enforce foreign keys by default in any case, unless you issue
PRAGMA foreign_keys=true.
I think this results in problems later on: when I activate the
relationships, I cannot do
v=User(username=v)
session.add(v)
session.commit()
c2=Contact(contactname=c2,owner=v)
session.add(c2)
session.commit()
sqlalchemy.exc.IntegrityError: (IntegrityError) contact.ownername may
not be NULL u'INSERT INTO contact (contactname, ownername) VALUES
(?, ?)' ('c2', None)
which doesn't seem normal.
Here is the code:
from datetime import datetime
from sqlalchemy import (create_engine, MetaData, Table, Column,
Integer,
String, DateTime, Float, ForeignKey, and_, select)
from sqlalchemy.orm import mapper, relationship, create_session,
column_property,sessionmaker
from sqlalchemy.ext.associationproxy import AssociationProxy
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
#engine = create_engine('sqlite:///test.db', echo=True)
metadata = MetaData(engine)
Session = sessionmaker(bind=engine)
session=Session()
Base = declarative_base(bind=engine, metadata=metadata)
class User(Base):
__tablename__ = 'user'
username = Column(String(50), primary_key=True)
contactname = Column(String(50),
ForeignKey('contact.contactname'), nullable=True)
class Contact(Base):
__tablename__ = 'contact'
contactname = Column(String(50), primary_key=True)
ownername = Column(String(50), ForeignKey('user.username',
use_alter=True, name=fk_owner_constraint), nullable=False)
#contacted_users = relationship(User, backref=contact,
post_update=True, primaryjoin=contactname==User.contactname)
#owner = relationship(User, backref=owned_contacts,
post_update=True, primaryjoin=User.username==ownername)
metadata.create_all(engine)
--
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] SELECT ARRAY(SELECT ...
Can someone show me the gist of how to construct an SA query that produces SQL* of the form SELECT ARRAY(SELECT ... FROM ... WHERE ...) as array_col Thanks, Michael * http://www.postgresql.org/docs/8.4/interactive/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS -- 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] Re: sqla and firebird
On Tue, Nov 9, 2010 at 4:10 PM, Domingo Aguilera domingo.aguil...@gmail.com wrote: Werner, I am using kinterbasdb downloaded just few days ago. Also I am using sqla 0.6.5 . I worked with firebird rdbms engine several years ago but not from python. This is the first time I am using firebird from python and from sqlalchemy ( been using python since 2004 ). I have an app in which every table is created with declarative_base and in every engine I've tried, it was enough to write... id = Column( Integer, primary_key = True ) This caused every engine to use whatever is needed to have an autoincrement field. In the case of firebird+kinterbasdb it was necessary to add the Sequence in the argument of Column as ... id = Column( Integer, Sequence(whatever, optional = True ), primary_key = True ) This is documented here: http://www.sqlalchemy.org/docs/orm/tutorial.html#define-and-create-a-table -- 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] Re: SELECT ARRAY(SELECT ...
This works:
select([
func.ARRAY(
select([t.c.value])
.where(t.c.id3)
.as_scalar()
)
.label('array_col')
])
- Gulli
On Nov 9, 3:43 pm, Michael Hipp mich...@hipp.com wrote:
Can someone show me the gist of how to construct an SA query that
produces SQL* of the form
SELECT ARRAY(SELECT ... FROM ... WHERE ...) as array_col
Thanks,
Michael
*http://www.postgresql.org/docs/8.4/interactive/sql-expressions.html#S...
--
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] Multiple request in the same controller causing problems with SQLAlchemy Session object
Hello, Latelly I've been running into this issue: OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') SELECT... To contextualize, I developed a Pylons application using scoped_session (default in pylons). And it was working until I created some extra processes of my application. Thanks Alan -- 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] Multiple request in the same controller causing problems with SQLAlchemy Session object
On Nov 9, 2010, at 4:56 PM, Alan Castro wrote: Hello, Latelly I've been running into this issue: OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') SELECT... To contextualize, I developed a Pylons application using scoped_session (default in pylons). And it was working until I created some extra processes of my application. DBAPI connections don't travel well over process boundaries. You need to call create_engine() inside each new child process. Thanks Alan -- 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] Multiple request in the same controller causing problems with SQLAlchemy Session object
Hi Alan We're also doing battle with this one.. One thing I did find was if I had large(ish) BLOB values, MySQL would go away. I found that I had to tweak some settings on MySQL to allow for larger sizes. (The default settings appear to be ridiculously small). Let me know what else you find, as it is a very annoying issue for us as well. BTW: What connector are you using? Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com On 10/11/2010, at 7:56 AM, Alan Castro wrote: Hello, Latelly I've been running into this issue: OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') SELECT... To contextualize, I developed a Pylons application using scoped_session (default in pylons). And it was working until I created some extra processes of my application. Thanks Alan -- 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] (Newbie) Using a custom collection extending from a dict(). Is that doable?
On Nov 8, 2010, at 6:36 PM, Hector Blanco wrote: methods that I have implemented and that need to be there. That would be the ZepConnector (and, for purposes of the example, it's method foo() it's the one I need to use). As you can see in the following lines, I randomly test its availability in the addChild1() method of the Parent. child1 = relationship( Child, uselist=True, primaryjoin=lambda: and_((Parent.id == Child.parent_id), (Child.type == VR)), collection_class=ZepConnector(VR) ) So this is incorrect - collection_class takes a class or other callable as an argument that will produce an instance of your collection. The ZepConnector source you have below indicates that ZepConnector(VR) is an instance of the collection. You need to use a lambda: there. The other errors you're getting would appear to extend from that (and is also why __init__ is called on ZepConnector - you're calling it yourself). -- 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] (Newbie) Using a custom collection extending from a dict(). Is that doable?
Shoot!! It works!! :D :D -- Parent.py (extract) -- # . . . child1 = relationship( Child, uselist=True, primaryjoin=lambda: and_((Parent.id == Child.parent_id), (Child.type == VR)), collection_class=lambda: ZepConnector(VR) ) child2 = relationship( Child, uselist=True, primaryjoin=lambda: and_((Parent.id == Child.parent_id), (Child.type == CC)), collection_class=lambda: ZepConnector(CC) ) --- Do you know any documentation where I can understand why? Conor already mentioned (http://groups.google.com/group/sqlalchemy/msg/d8fbbbff6d961332) the importance of using lambdas, strings... to avoid mistakes, -- Quote: - In SQLAlchemy you get around circular dependencies by: * Using strings as the target of ForeignKey() * Using class name strings as the target of a relation (declarative only) * Using strings or callables as primaryjoin/secondaryjoin arguments in a relationship() - but I'd like to understand a little bit more how does it work (what's going on internally) so I won't make similar errors in the future. Thank you so much... 2010/11/9 Michael Bayer mike...@zzzcomputing.com: On Nov 8, 2010, at 6:36 PM, Hector Blanco wrote: methods that I have implemented and that need to be there. That would be the ZepConnector (and, for purposes of the example, it's method foo() it's the one I need to use). As you can see in the following lines, I randomly test its availability in the addChild1() method of the Parent. child1 = relationship( Child, uselist=True, primaryjoin=lambda: and_((Parent.id == Child.parent_id), (Child.type == VR)), collection_class=ZepConnector(VR) ) So this is incorrect - collection_class takes a class or other callable as an argument that will produce an instance of your collection. The ZepConnector source you have below indicates that ZepConnector(VR) is an instance of the collection. You need to use a lambda: there. The other errors you're getting would appear to extend from that (and is also why __init__ is called on ZepConnector - you're calling it yourself). -- 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] Re: SELECT ARRAY(SELECT ...
On 11/9/2010 3:55 PM, Gunnlaugur Briem wrote:
select([
func.ARRAY(
select([t.c.value])
.where(t.c.id3)
.as_scalar()
)
.label('array_col')
])
Thank you! That works swimmingly.
I now know why my search didn't turn up anything as it is evidently one of
those If the function name is unknown to SQLAlchemy, it will be rendered
exactly as is.
Thanks,
Michael
--
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] Re: Multiple request in the same controller causing problems with SQLAlchemy Session object
Hi guys, An update: Mike is right! The connector doesn't get along with child processes... For a quick setup and since I'm using uWSGI and Nginx, I just created a cluster o uwsgi connections and ran 3 independent process of my application. All my tests passed nicely. @Warwick I'm using mysql-python (MySQLdb). And try to follow what Mike said and test it and see if you still get the errors. Tomorrow I'll run more tests at work but I think it is solved. Thanks Mike! Regards Alan On 9 nov, 20:10, Warwick Prince warwi...@mushroomsys.com wrote: Hi Alan We're also doing battle with this one.. One thing I did find was if I had large(ish) BLOB values, MySQL would go away. I found that I had to tweak some settings on MySQL to allow for larger sizes. (The default settings appear to be ridiculously small). Let me know what else you find, as it is a very annoying issue for us as well. BTW: What connector are you using? Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com On 10/11/2010, at 7:56 AM, Alan Castro wrote: Hello, Latelly I've been running into this issue: OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') SELECT... To contextualize, I developed a Pylons application using scoped_session (default in pylons). And it was working until I created some extra processes of my application. Thanks Alan -- 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 athttp://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] Re: FlushError: Instance has NULL identity
While the above code uses some patterns that are unnecessary, there's nothing about it which would cause that error to occur. To my knowledge, the only way that particular condition arises is if a flush occurs at an inappropriate time, namely within an object load, which does not seem to be the case based on your stack trace. So I am extremely curious how you're getting that to occur. I am curious myself. Its exactly like in the given example. Sometimes the exception seem to raise randomly. Sometimes the second page of the given RSS is saved the way it should. Most time not though. The advised way to do entity name, which is roughly the pattern you have above, that is, one which maps the same class to multiple tables, is to create new classes for each table. These can be ad-hoc subclasses of MyModel if the base class has other significant behaviors. A description of this is athttp://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName. You'd probably want to pull your mapped classes from a dictionary, and if not present *then* create the Table object and mapper - the regeneration of the Table above with useexisting is not an optimal way to do things. All right. I am going to use this way then. I don't like to use useexisting as well, but didn't find a better way. Thanks to point it out. -- 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] Practical ways for dealing with concurrency problems?
I've got several questions about dealing with concurrency issues in
SQLAlchemy. To try and help with the asking, I'll use a small example
similar to a situation I've got in an SQLAlchemy application. In this
application, there is a table that tracks objects and their order/
position, something like below...
Name Pos
---
ObjA1
ObjB2
ObjC3
And this can change to the following with a new object inserted
before (position-wise) ObjB...
Name Pos
---
ObjA1
ObjB3
ObjC4
NewObj 2
Some sample code is whipped up to do this is at the bottom of this
post.
Worth noting is that my application is currently database-agnostic
(SQLAlchemy enabling this is a great feature), and I'd like to keep it
that way if at all possible.
My questions revolve around concurrency issues because the insert
operation can potentially update all rows in the table. It seems that
this is very vulnerable to concurrency problems since each transaction
will only update its own rows that existed at the start of the
transaction. If a parallel transaction is started doing a similar
insertion, a record can be potentially get a duplicate position
number, which would destroy the application logic.
I'm much more of an application person than a DB person, so to solve
this my immediate thinking is to simply do it all at the application
layer and use a thread lock on the table updating function. All table
access is routed through the application (which uses scoped sessions)
so this would certainly work, but I'm certain that those more
familiar with sqla and the DB layer would likely cringe at that
approach.
I really want to learn more about dealing with concurrency at the
database level through sqlalchemy. What is a good way to deal with
this in sqla?
One possibility seems to be to get it all done in one atomic commit.
But how can I use the ORM (together with the expression language?) to
increment by 1 all positions where the position value is than the
position for a given object name? For a single value, the docs show
me that can get the proper SQL emitted for a record with something
like:
obj.position = obj.__table__.c.position + 1
where obj is one of my mapped objects. But how do I do it for
multiple rows and not just the one obj? Also - is going through
__table__ the most direct way for achieving this?
After the application layer blocking, the next most brutal thing that
comes to mind is locking the entire table during the operation to
avoid problems. I'm not clear this will work since it seems a read
lock is needed, and I don't yet know if you can actually do that. It
may be irrelevant, since I certainly do not see any way of doing table
locks through sqla at the moment. If locking were an option, it seems
it would require direct (potentialy DB specifc) SQL statement
execution through session.execute() or conn.execute(). Is that
correct?
Something that I've seen referenced a few times for dealing with
concurrency in sqlalchemy is to use with_lockmode() on a query to
lock the relevant rows. However - I'm wary of this for a few
reasons: First is that I don't think it would work in my example case
because read blocking is needed (I think). Second is that I can't
actually figure out what with_lockmode actually does. Digging in the
code I see the lock options are read, update, update_nowait.
Short of finding this in query.py, where are the docs for these
options?? The third bit of wariness is uncertain support for
different dialects. Digging through my 'SQL in a Nutshell' I see
varying DB support for SELECT FOR UPDATE syntax (which is what
with_lockmode seems to employ?) across various RDMS so I'd rather stay
away from it if possible. What is the best way to figure out the
database dialect support for each of these options, short of trying
with all potential database types?
Although I'm certainly interested in specific ideas on dealing with
something similar to the table update described, any links or tips
anyone has for dealing with concurrency in SQLAlchemy would be
appreciated. This includes any expansions anyone has on how the UOW
pattern and ORM transactions alone help with some forms of
concurrency, as I know they do to some extent.
Thanks,
Russ
## sample code with concurrency issue...
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData
from sqlalchemy import create_engine
from pprint import pprint
engine = create_engine('sqlite:///:memory:', echo = False)
metadata = MetaData()
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
Base = declarative_base()
class OrderedObject(Base):
__tablename__ = 'objects'
id = Column(Integer, primary_key = True)
name = Column(String)
position = Column(Integer)
def __init__(self, name, name_to_bump = ):
self._ormSess = Session() #does embedding this scare anyone?
self.name = name
if name_to_bump:
