[sqlalchemy] SQL Alchemy is bugging out my Redshift table.
Hi everyone.
I've been experiencing a very weird issue, and I hope someone could help me
with it.
I've mapped a Redshift table using SQL Alchemy's ORM.
Here is my code.
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import psycopg2
user = ""
passwd = ""
host = ""
engine = create_engine('postgresql+psycopg2://{}:{}{}'.format(user,passwd,
host))
Base = automap_base()
class Bookmarks(Base):
__tablename__ = 'my_table'
__table_args__ = {"schema":"dev"}
agent_id = Column(Integer, primary_key=True)
username = Column(String)
email = Column(String)
first_name = Column(String)
last_name = Column(String)
role_id = Column(Integer)
role = Column(String)
team = Column(String)
access_level = Column(String)
location = Column(String)
def loadSession():
""
metadata = Base.metadata
Session = sessionmaker(bind=engine)
session = Session()
return session
Base.prepare()
session = loadSession()
res = session.query(Bookmarks).all()
Everything works fine after executing the code. The problem is that it
looks like it's been interacting weirdly with AWS. I've been creating
tables to test this out, and it seems like everytime I map a Redshift table
using this code, the table becomes totally unresponsive. I'm able to query
it using SQLAlchemy, but if I try to alter it, or delete it, it will
literally take a hour before getting a response.
I'm not getting any errors, it's just that everything becomes very slow.
The tables are very small, so this shouldn't be happening.
Has anyone experienced this issue?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] how to properly remove a one-to-one relationship ?
Hello all, What is the proper way to fully remove a one-to-one related object ? I I do Session.delete(a.b) print a.b # b is still here ... What am I missing ? Thanks, Laurent -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] How-to filter by the time part of a datetime field?
Hi, I've a model with a DateTime column and I need to select rows where the time part of this column is between two hours (something like: select all rows where the date is between 6:00 and 11:00). I need the date information, rows must be deleted after a couple of days. I don't know how-to extract the time part of my DateTime field in .filter(). Should I add a Time column ? Or is it possible to use only the DateTime column? Thanks. -- Laurent Meunier laur...@deltalima.net -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] How-to filter by the time part of a datetime field?
Hi Warwick, On 30/08/2013 14:38, Warwick Prince wrote: I'm sure there is a better way, but you could always filter using a date/time and supply the date part as well (i.e. today) so that you are comparing datetime to datetime. (Something like: select all rows where the datetime is between 2013-08-30 06:00:00 and 2013-08-30 11:00:00) :-) Thanks for your suggestion, this could do the trick. However my rows are split over a lot of days and if I follow your advice I'll end with a lot of between 2013-08-30 06:00:00 and 2013-08-30 11:00:00 (one for each day). This will certainly work as expected, but I'm looking for a more efficient way of doing this. Thanks. -- Laurent Meunier laur...@deltalima.net -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] How-to filter by the time part of a datetime field?
Wonderfull! Exactly what I was looking for. Thank you Jeff.
I was searching in the sqlalchemy documentation for a time function,
but haven't found one. In fact, you can use any function supported by
the database directly with `func`. So I can use `func.time` and this
will use the TIME function of sqlite ... awesome! I've learnt something
new today :)
On 30/08/2013 15:53, Jeff Peck wrote:
One way to do this is to use a function within your database to convert a
timestamp down to a basic time type, and then do comparison on the converted
value. Here is an example using sqlite as the back end. Sqlite has a time
function that can convert a datetime down to a time for you, so we get at
that using sqlalchemy's func:
from sqlalchemy import create_engine, Column, Integer, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///test.sqlite')
Session = sessionmaker(bind=engine)
Base = declarative_base()
class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
timestamp = Column(DateTime, nullable=False)
Base.metadata.create_all(bind=engine)
# This is just sticking random timestamps into the database...
import datetime
import random
session = Session()
session.query(Test).delete()
for i in range(100):
d = random.randint(1, 30)
h = random.randint(0, 23)
m = random.randint(0, 59)
test = Test()
test.timestamp = datetime.datetime(2013, 8, d, h, m)
session.add(test)
session.commit()
# Heres the important part. Pull in func
from sqlalchemy import func
# Say we want any timestamp in the db regardless of date where the time
# is between 12:00 and 12:30
t1 = datetime.time(12, 00)
t2 = datetime.time(12, 30)
query = session.query(Test).filter(func.time(Test.timestamp).between(t1,
t2))
for row in query.all():
print(row.timestamp)
Regards,
Jeff Peck
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Warwick Prince
Sent: Friday, August 30, 2013 8:01 AM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] How-to filter by the time part of a datetime
field?
Hi Warwick,
On 30/08/2013 14:38, Warwick Prince wrote:
I'm sure there is a better way, but you could always filter using a
date/time and supply the date part as well (i.e. today) so that you are
comparing datetime to datetime. (Something like: select all rows where the
datetime is between 2013-08-30 06:00:00 and 2013-08-30 11:00:00) :-)
Thanks for your suggestion, this could do the trick.
However my rows are split over a lot of days and if I follow your advice
I'll end with a lot of between 2013-08-30 06:00:00 and 2013-08-30 11:00:00
(one for each day). This will certainly work as expected, but I'm looking
for a more efficient way of doing this.
Thanks.
--
Laurent Meunier laur...@deltalima.net
Ahh - I see. Oh well, I'm sure someone with infinitely better SQL skills
with chime in shortly. :-)
Warwick
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: Modeling a Tree-looking structure in SqlAlchemy.
Hello, You should also take a look at http://pypi.python.org/pypi/sqlamp/0.5.2, an implementation of Materialized Path for SQLAlchemy. Regards, Laurent Le 13 déc. 2010 à 23:30, Russell Warren a écrit : Sorry, I just saw I messed up the nested sets SQLA example link. Here is the right one: http://www.sqlalchemy.org/trac/browser/examples/nested_sets/nested_sets.py -- 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: find the table columns
On Thu, 2010-01-14 at 05:29 -0800, Kosu wrote:
try this:
user.__table__.c.keys()
should work
Kos Rafal
AttributeError: 'User' object has no attribute '__table__'
thanks anyway
On 14 Sty, 11:39, laurent FRANCOIS lau.franc...@worldonline.fr
wrote:
Hello everybody
Let's say I have a
table_user= Table('user', blablabla...)
class User(object): pass
mapper(User, table-user)
Let's say I have an ORM object: user = User(name='FOO')
With that user I would like to get all the attribut columns like
with table_user.c.keys().
Must be easy no?
Thanks
Laurent FRANCOIS
--
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] find the table columns
Hello everybody
Let's say I have a
table_user= Table('user', blablabla...)
class User(object): pass
mapper(User, table-user)
Let's say I have an ORM object: user = User(name='FOO')
With that user I would like to get all the attribut columns like
with table_user.c.keys().
Must be easy no?
Thanks
Laurent FRANCOIS
--
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] more than one one to many relation
Hello,
What is the pattern for more than one relation one to many.
I have on parent with 2 childs.
parent_table = Table('parent', metadata,
Column('parent_id', Integer, primary_key=True)
)
child1_table = Table('child1', metadata,
Column('child1_id', Integer, primary_key=True),
Column('parent_id', Integer,ForeignKey(parent_id))
child2_table = Table('child2', metadata,
Column('child1_id', Integer, primary_key=True),
Column('parent_id', Integer,ForeignKey(parent_id))
class Parent(object): pass
class Child1(object): pass
class Child2(object): pass
parent_map = mapper(Parent, parent_table,
properties={'child1':relation(Child1), 'child2':relation(Child2)})
child1_map = mapper(Child1, child1_table)
child2_map = mapper(Child2, child1_table)
This mapper coding doesn't work?
What is the right way to do that?
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=.
[sqlalchemy] Re: setattr and mapped ORM
On Wed, 2009-10-21 at 09:24 -0700, David Gardner wrote: Using setattr() shouldn't be a problem, however the __dict__ attribute also has other things in there like _sa_instance_state that I don't believe you want to copy to the new item. Try this instead: for col in object_mapper(update): setattr(proxy,col.key,getattr(update,col.key)) Hum hum Object Mapper is not Callable So I don't understand what you mean. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: setattr and mapped ORM
On Wed, 2009-10-21 at 09:24 -0700, David Gardner wrote: Using setattr() shouldn't be a problem, however the __dict__ attribute also has other things in there like _sa_instance_state that I don't believe you want to copy to the new item. Try this instead: for col in object_mapper(update): setattr(proxy,col.key,getattr(update,col.key)) OK thanks. After doing some introspection: proxy User record: None None update User record: francois tretre for c in user_mapper.c: ... setattr(proxy, c.key, getattr(update, c.key)) ... proxy User record: francois tretre And of course user_mapper = mapper(User, user_table) L.FRANCOIS --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Update session extension after creation
Hi, I'm currently using a webframework which uses sqlalchemy. Each time a request hits the server, the framework creates a session object I can use during the page creation. I wish you could update this session with one of my SessionExtension but I'm facing a small problem: I tested my code with a standalone session I had created with this syntax : session = create_session(extension=MySessionExtension()) and everything works. When I try the same code in my web context and I do: from framework.db import session session.extension = MySessionExtension() . . . None of my extension session methods are called. Any idea ? --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Update session extension after creation
Hi, Thank you for your interest. Testing with this kind of declaration : session.extensions = [VersionedListener()] doesn't change anything and using this kind of declaration : session.extensions.append(MySessionExtension()) returns: AttributeError: 'ScopedSession' object has no attribute 'extensions' = Sounds like my scoped session doesn't have any extension at all (even empty) Regards, Laurent On 10 sep, 14:09, Mike Conley mconl...@gmail.com wrote: Well, it looks like configure is a class method on Session, so when you do session.configure() you are configuring future sessions, not the current one. The extensions for a session instance are are a property named extensions. You could try setting that list yourself. session.extensions = [MySessionExtension()] to replace the existing extension or session.extensions.append(MySessionExtension()) to add yours to the list But understand the risk that this is modifying internals and might not work, and no guarantee it will work in future versions. -- Mike Conley On Thu, Sep 10, 2009 at 4:29 AM, asrenzo laurent.rah...@gmail.com wrote: I also tried session.configure(extension=MySessionExtension()) with no success On 10 sep, 10:13, Laurent Rahuel laurent.rah...@gmail.com wrote: Hi, I'm currently using a webframework which uses sqlalchemy. Each time a request hits the server, the framework creates a session object I can use during the page creation. I wish you could update this session with one of my SessionExtension but I'm facing a small problem: I tested my code with a standalone session I had created with this syntax : session = create_session(extension=MySessionExtension()) and everything works. When I try the same code in my web context and I do: from framework.db import session session.extension = MySessionExtension() . . . None of my extension session methods are called. Any idea ? --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] : Declarative and association object same model
Hi,
I need to implement the storage of the neighborood of a place. I try to
use Declarative and relation to get this done but it sounds like I
missed something important I can not understand.
Here are my models:
neighbors_table = Table('neighbors', Base.metadata,
Column('shop_id', Integer, ForeignKey('places.id'), primary_key=True),
Column('neighbor_id', Integer, ForeignKey('places.id')),
)
class Place(Base):
__tablename__ = 'places'
name = Column(Unicode(255), nullable=True)
neighbors = relation('Place', secondary=neighbors_table)
But as soon as I try to use a Place object, I get this error:
sqlalchemy.exc.ArgumentError: Could not determine join condition between
parent/child tables on relation Shop.neighbours. Specify a
'primaryjoin' expression. If this is a many-to-many relation,
'secondaryjoin' is needed as well.
I tried to add a primaryjoin and a secondaryjoin but with no success.
Sounds like I don't understand what are primaryjoin and a secondaryjoin
expressions.
Any idea ?
Regards,
Laurent
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: : Declarative and association object same model
Thanks Michael, Everything is OK now. Sorry for the typo, I was renaming my classes. Regards, Laurent Le 04/09/2009 16:30, Michael Bayer a écrit : asrenzo wrote: Here is a primaryjoin, secondaryjoin I tried without success: neighbors = relation(Place, primaryjoin=(Place.id == neighbors_table.place_id), secondaryjoin= (neighbors_table.neighbor_id == Place.id), secondary=neighbors_table) and the error is: sqlalchemy.exc.InvalidRequestError: When compiling mapper Mapper|Shop| shops, expression 'Shop.id == neighbours_table.shop_id' failed to locate a name (name 'neighbours_table' is not defined). If this is a class name, consider adding this relation() to theclass 'shoplocator.orm.shop.Shop' class after both dependent classes have been defined. Which I can't understand because neighbors table is defined before the Place class definition. the first strange thing is the message says Shop.id but the string you are showing says Place.id. Anyway, the Table object neighbors_table is not part of the locals() when the string expressions are evaluated. therefore just don't use string arguments for primaryjoin/secondaryjoin/secondary, use the expression directly (i.e. secondary=neighbors_table, primaryjoin=id==neighbors_table.c.neighbor_id). --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] ForeignKey on a ForeignKey
Hi all,
I'm stucked with a problem I'm not able to solve (SA 0.5.2). I hope on
of you would be smarter than me.
Here is the problem:
import sqlalchemy as sa
__metadata__ = sa.MetaData()
OBJECTS = sa.Table('objects', __metadata__,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('parent_id', sa.ForeignKey('objects.id',
ondelete='CASCADE', onupdate='CASCADE')),
sa.Column('name', sa.Text, index=True, nullable=False, default='Root'),
)
OBJECTSTREE = sa.Table('objects_tree', __metadata__,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('child_id', sa.ForeignKey('objects.id',
ondelete='CASCADE', onupdate='CASCADE'), primary_key=True),
sa.Column('parent_id', sa.ForeignKey('objects.parent_id',
ondelete='CASCADE', onupdate='CASCADE'), primary_key=True),
sa.Column('depth', sa.Integer, nullable=False, index=True, default=0),
)
When I call the create_all() method from metadata, I always get this
error - sqlalchemy/types.py, line 375, in get_col_spec raise
NotImplementedError()
I sounds that my parent_id column from table objects_tree can not be
created because it's a ForeignKey on another ForeignKey.
When I use plain SQL on my pgsql shell, everything is OK.
Does anybody have any clue ?
Regards,
Laurent
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Migration from django orm: override save method?
Hi, You should take a look at MapperExtension. Then you'll be able to add method which would be called depending on the orm actions. For example, you'll be able to add your own custom before_insert or after_insert or after_delete methods. Regards, Laurent Le 15/06/2009 17:36, Didip Kerabat a écrit : Quick note about __init__ method. SA select(..) or query(...) does not call __init__(), so your to upper logic won't be executed then. If you want it to be called every object construction you need to do this: from sqlalchemy import orm @orm.reconstructor def some_function(): self.field1=field1.upper() # Call that method inside __init__ as well def __init__(self): some_function() Sorry for not answering the problem. - Didip - On Mon, Jun 15, 2009 at 1:43 AM, drakkan drakkan1...@gmail.com mailto:drakkan1...@gmail.com wrote: Hi all, I'm migrating from django orm to sqlalchemy, in django was very simple to override save method to do own stuff before save the value to database, for example: class TestModel(models.Model): field1=models.CharField(max_length=255) def save(): self.field1=self.field1.upper() super(TestModel,self).save() so when I add or modify an object it is ever converted to uppercase. In sa this simple model become: class TestModel(Base) __tablename__='testtable' field1=Column(Unicode(255)) def __init__(field1): self.field1=field1.upper() this way if I call the init method field1 is converted to upper but if i modify the field I have to manually convert to upper. There is some way to override save method as in django orm? regards drakkan --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAchemy behevior
Hi,
As I ran into the same kind of problem some times ago, you should take a
look at mapper or session extensions.
As an example, you could take a look at MapperExtension and add some
methods like
class MyModelExtension(MapperExtension):
def before_insert(self, mapper, connection, instance):
some kind
of code
def after_insert(self, mapper, connection, instance):
some kind
of code
def after_delete(self, mapper, connection, instance):
some kind
of code
and a Declarative Model like this:
class MyModel(Base):
__tablename__ = 'mymodel_table'
id = Column(Integer, primary_key=True)
slug = Column('type', String(50))
...
__mapper_args__ = {'extension': MyModelExtension()}
Then everything should be OK
Regards,
Laurent
Tcourbon a écrit :
Hi there !
I'm new to SQLAlchemy (and python actually) and I read through the
documentation as I'm using SQLAlchemy (and Pylons) to run a database
driven website. As I come from php, my former ORM of choice was
doctrine (http://www.doctrine-project.org/). One of the features I
used to love was behavior (http://www.doctrine-project.org/
documentation/manual/1_1/en/behaviors). In short it's the ability to
alter query/data/whatever-you-want-here before and after a query is
executed. For instance, automatic slug generation (which is what I'm
looking to use/implement currently).
I wonder if their is something similar in SQLALchemy, or if not, if
their is some hook (let's say something like a preInsert or preUpdate
method in Table) I could use to customize my model.
Thank,
Thomas
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Self-referential with DeclarativeBase
Hi,
As you are defining Country when you use it within your relation, you
need to define your relation like this :
parent = relation(Country, backref=backref('child'), cascade=all)
Note the quotes around Country.
Regards,
Laurent
Cedric a écrit :
Hi,
I'm trying to migrate my TurboGears project from SQLObject to
SQLAlchemy. As the TurboGears2 way of using SQLAlchemy is the
DeclarativeBase, I'm trying to achive a self-referential using the
DeclarativeBase. Following the documentation, it seems that the
following code could be correct (after a translation from the Base
example), but in fact, it complains that the Country name is not
defined...
File /is2dev/dev/model/model.py, line 47, in Country parent =
relation(Country, backref=backref('child'), cascade=all)
NameError: name 'Country' is not defined
I just try to make a tree with the world continents regions
countries.
class Country(DeclarativeBase):
Country with iso and related region
__tablename__ = 'countries'
#{ Columns
id = Column(Integer, primary_key=True)
name = Column(Unicode(100), nullable=False)
iso = Column(Unicode(3), nullable=False)
created = Column(DateTime(), default=datetime.now(),
nullable=False)
updated = Column(DateTime(), nullable=True)
#{ Relations
parent_id = Column(Integer, ForeignKey('countries.id'),
nullable=True)
parent = relation(Country, backref=backref('child'),
cascade=all)
#{ Special methods
#}
Thanks for your help...
Regards
Cédric
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Session and pre/post callable methods
Thanks for this quick response Michael Bayer a écrit : look into using MapperExtension and/or SessionExtension to add hooks within the flush process. Laurent Rahuel wrote: Hi, I'm currently trying to port a Django based application using sqlalchemy and I'm a bit confused with some Django's orm features I'm not able to find in sqlalchemy. To avoid triggers I used to redefine the save() and delete() methods from Django's models to be able to update some other tables not in relation with the object currently manipulated. Using sqlalchemy, every database querying is done via the session object and thus, save() and delete() are not linked to the manipulated object anymore. So my question is : Is there any kind of _pre or _post method I can define in my Declarative models to be able to do the same ? Regards, Laurent --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Session and pre/post callable methods
Hi, I'm currently trying to port a Django based application using sqlalchemy and I'm a bit confused with some Django's orm features I'm not able to find in sqlalchemy. To avoid triggers I used to redefine the save() and delete() methods from Django's models to be able to update some other tables not in relation with the object currently manipulated. Using sqlalchemy, every database querying is done via the session object and thus, save() and delete() are not linked to the manipulated object anymore. So my question is : Is there any kind of _pre or _post method I can define in my Declarative models to be able to do the same ? Regards, Laurent --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] foreignkey and relation synchronization
Hello, There's a behaviour in SA that is not clear to me: if we look at the example (User and Address) from the docs, we can change either the related object or the foreign key For example: ad = Address(email_address='j...@google.com') ad.user = jack OR ad.user_id = 1 What is the prefered behaviour ? And what happens if the two fields are not in synch ? (ie, what if jack.id=1 and we set explicetly ad.user = 2) Thanks for clarifying . --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy]
Hello
Is it obvious that this model:
%
##Class Table
user_table = Table(
'user', metadata,
Column('user_id', Integer, primary_key=True),
Column('fname', Unicode(50), default=''),
Column('lname', Unicode(50), default='')
)
## Class ORM
class User(object):
uUser model
def __init__(self,dic):
Set instance attribut with a dictionnary
dico= {'fname': 'value1', 'lname': 'value2'}
instance = User(dico)
self.listfieldname = dic.keys()
self.dic = dic
for key in dic:
setattr(self,key,dic[key])
def __repr__(self):
return '%s %s' %(self.dic[self.listfieldname[0]],
self.dic[self.listfieldname[1]])
mapper(User, user_table)
%%
doesn't work for a session.query(User).all()
but work fine for session.add(User(dico))
???
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: session.merge()
Hi, Did you first load the entry to update into the session or do you always create new objects to be saved ? I guess you need to: 1 - try to get the record from the database using the product_id or another unique field 2 - if the result set is not empty, update the record using values from the XML and session.commit() will update the database else create a new mapped object with values from the XML file, session.add(ob), and session.commit() will add the record into database Regards, Laurent n00b a écrit : greetings, i'm batch processing xml documents to mysql using SA 0.5, ORM. data extracted from xml docs may be new or an update in form of a replacement of the existing object (record). (one of the columns, product_id, is unique=True). Hence, SA throws, as expected, an IntegrityError (1062, duplicate Entry) when i'm trying to commit the revised object via session.add(obj), session.commit(). given the xml volume at hand, i thought i could use session.merge rather than query every time for existence. alas, no go. get the same 1062 exception. obviously, i don't understand the session merge. in fact, even if i query for the existence, then try and assign the new object to the existing object (old_obj = session.query(OBJ).filter (OBJ.product_id == new_obj.product.id).one(), old_obj = new_obj, session.add(old_obj), session.commit()), i get the dreaded 1062. any insights/suggestions? thx --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Many to many and orphan deletion
On Jan 9, 9:24 pm, Michael Bayer [EMAIL PROTECTED] wrote: So if I understand what youre looking to do here, youd like a keyword to be deleted when the last parent is removed ? That is exactly what I would like... if youd like to check for this condition after each flush you can do it with SessionExtension, using after_flush(). it could be done in a single DELETE statement, i.e. delete from keywords where not exists(select 1 from keyword_associations where keyword_id=keywords.id) ...But I would also like to track tag deletion in SessionExtension, and If I'm using directly a DELETE statement, it won't trigger anything in SessionExtension. --~--~-~--~~~---~--~~ 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: Many to many and orphan deletion
On Jan 10, 4:00 pm, Michael Bayer [EMAIL PROTECTED] wrote: A statement issued in SessionExtension would fire unconditionally upon any flush(), so thats the trigger there. not sure what you mean by track here, if it means you want to know the keywords that were deleted, you'd just issue the above SELECT first, do something with the rows, then the DELETE. I want to know the keywords that were deleted, but it's more complicated... keywords are mapped to Keyword objects with another relation and cascading rules. I would like those cascading rules to apply, and know it in SessionExtension. --~--~-~--~~~---~--~~ 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: Many to many and orphan deletion
how about, go into sessionextension.after_flush(), make a new session local to the operation, issue a Query with the Select for all orphan keywords, delete them all and flush that sub-session, then expunge() those keywords from the parent session sent to after_flush, like this: [...] Brilliant !! I even can extend local session with the session extension itself in case of an orphan deletion deletes another orphan... --~--~-~--~~~---~--~~ 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: Many to many and orphan deletion
Can anybody help me ? ...No one ?? --~--~-~--~~~---~--~~ 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] Inheritance and relation in primary table
Hi,
I'm still experimenting SA features and, once again, I'm stucked with a
relation definition problem.
This is the deal.
Considering this tables definition :
USERS = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(255), nullable=False),
)
BASES = Table('bases', metadata,
Column('id', Integer, primary_key=True),
Column('owner_id', Integer, ForeignKey('users.id'), primary_key=True),
Column('creation_date', DateTime),
Column('modification_date', DateTime),
Column('type', String(255), nullable=False),
Column('title', String(255)),
)
FOLDERS = Table('folders', metadata,
Column('id', Integer, ForeignKey('bases.id'), primary_key=True),
Column('description', String()),
)
This class definitions :
#
# Class definition
#
class User(object):
pass
class Base(object):
pass
class Folder(Base):
pass
And this mappers :
base_join = polymorphic_union(
{
'folder':BASES.join(FOLDERS),
'base':BASES.select(BASES.c.type=='base'),
}, None, 'pjoin')
base_mapper = mapper(Base, BASES, select_table=base_join,
polymorphic_on=base_join.c.type, polymorphic_identity='base',
properties={'owner': relation(User,
primaryjoin=BASES.c.owner_id==USERS.c.id, backref='objects')})
mapper(Folder, FOLDERS, inherits=base_mapper,
polymorphic_identity='folder',)
mapper(User, USERS)
I guess my relation defined in the base_mapper has something wrong
because I'm not able to add an owner to my Bases objects.
Any idea ??
Regards,
Laurent.
--~--~-~--~~~---~--~~
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: Inheritance and relation in primary table
Here is an attached sample of my problem.
You can create a User, u, and a Folder, f. Assign ownership via a
simple f.owner = u.
You can do an :
objectstore.save(u)
objectstore.save(f)
You can see relations via
print f.owner
print u.objects
But this can not be saved into the database. An
objectstore.flush() will fail with a :
SQLError: (IntegrityError) bases.id may not be NULL 'INSERT INTO bases
(owner_id, creation_date, modification_date, type, title) VALUES (?, ?,
?, ?, ?)' [1, None, None, 'folder', Le titre de l'object]
Everything works fine as soon as you remove the owner relation and the
owner_id in the BASES table declaration.
Michael Bayer a écrit :
it all looks fine to me, youd have to show me something more specific.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
# -*- coding: UTF-8 -*-
# * BEGIN LICENSE BLOCK *
# This file is part of NOMDUPRODUIT.
# Copyright (c) 2004 laurent Rahuel and contributors. All rights
# reserved.
#
# NOMDUPRODUIT is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# NOMDUPRODUIT is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with NOMDUPRODUIT; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
# * END LICENSE BLOCK *
from sqlalchemy import mapper, relation, polymorphic_union, create_session
from sqlalchemy import BoundMetaData, Table, Column, Integer, DateTime, Date, Time, String, Boolean, Float, ForeignKey
from datetime import datetime
metadata = BoundMetaData(sqlite:///:memory:)
objectstore = create_session()
#
# Tables definition
#
USERS = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(255), nullable=False),
)
BASES = Table('bases', metadata,
Column('id', Integer, primary_key=True),
Column('owner_id', Integer, ForeignKey('users.id'), primary_key=True),
Column('creation_date', DateTime),
Column('modification_date', DateTime),
Column('type', String(255), nullable=False),
Column('title', String(255), nullable=False),
)
FOLDERS = Table('folders', metadata,
Column('id', Integer, ForeignKey('bases.id'), primary_key=True),
Column('description', String()),
)
#
# Class definition
#
class User(object):
pass
class Base(object):
pass
class Folder(Base):
pass
mapper(User, USERS)
base_join = polymorphic_union(
{
'folder':BASES.join(FOLDERS),
'base':BASES.select(BASES.c.type=='base'),
}, None, 'pjoin')
base_mapper = mapper(Base, BASES, select_table=base_join, polymorphic_on=base_join.c.type, polymorphic_identity='base',properties={'owner': relation(User, primaryjoin=BASES.c.owner_id==USERS.c.id, backref='objects')})
mapper(Folder, FOLDERS, inherits=base_mapper, polymorphic_identity='folder',)
metadata.drop_all()
metadata.create_all()
u = User()
u.name = Laurent
f = Folder()
f.title = Le titre de l'object
f.owner = u
[sqlalchemy] Re: Inheritance and relation in primary table
Tried with a postgresql database and I get another error right in my metadata.create_all(). I get a SQLError: (ProgrammingError) from postgres telling me I got something wrong while creating constraint on unexisting key into table bases. CREATE TABLE folders ( id INTEGER NOT NULL, description TEXT, FOREIGN KEY(id) REFERENCES bases (id), PRIMARY KEY (id) ) But if I remove owner_id from BASES and I remove the relation in base_mapper, I get all tables created. Any idea ?? Michael Bayer a écrit : sqlite's autoincrement feature does not work when you define a composite primary key. youll have to set the id attribute on each instance manually before flushing. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
