[sqlalchemy] order_by column in adjoining table
Given the following classes/tables:
class TagList(Base):
TagID = Column(Integer, primary_key=True)
trafficInputs = relationship('TrafficInput', secondary='TagCaseLink',
order_by='Traffic.Time')
class TagCaseLink(Base):
TagID = Column(Integer, ForeignKey('TagList.TagID'), primary_key=True)
TrafficInputID = Column(Integer,
ForeignKey('TrafficInput.TrafficInputID'))
class TrafficInput(Base):
TrafficInputID = Column(Integer, primary_key=True)
TrafficDeviceID = Column(Integer,
ForeignKey('TrafficDevice.TrafficDeviceID'), nullable=False)
ConfigID = Column(Integer, ForeignKey('Config.ConfigID'),
nullable=False)
class TrafficDevice(Base):
TrafficDeviceID = Column(Integer, primary_key=True)
TrafficID = Column(Integer, ForeignKey('Traffic.TrafficID'),
nullable=False)
DeviceID = Column(Integer, ForeignKey('Device.DeviceID'),
nullable=False)
class Traffic(Base):
TrafficID = Column(Integer, primary_key=True)
TrafficName = Column(String, nullable=False)
Time = Column(DateTime, nullable=False)
I get the following error: sqlalchemy.exc.ProgrammingError:
(ProgrammingError) missing FROM-clause entry for table Traffic
So TagList contains a list of trafficInputs that I want ordered by
Traffic.Time. Is there anyway of doing this?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] mysql table partitioning in SA
Hello, MySQL supports partitioning: http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html It requires special CREATE TABLE statement. Is it possible to make use of this partitioning in SA? How to do this? Regards, Godfryd -- 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] mysql table partitioning in SA
these keywords aren't built into the mysql options at the moment so here is a
@compiles recipe:
from sqlalchemy import *
from sqlalchemy.schema import CreateTable
from sqlalchemy.ext.compiler import compiles
@compiles(CreateTable, mysql)
def add_partition_scheme(element, compiler, **kw):
table = element.element
partition_by = table.kwargs.pop(mysql_partition_by, None)
partitions = table.kwargs.pop(mysql_partitions, None)
ddl = compiler.visit_create_table(element, **kw)
ddl = ddl.rstrip()
if partition_by:
ddl += \nPARTITION BY %s % partition_by
table.kwargs['mysql_partition_by'] = partition_by
if partitions:
ddl += \nPARTITIONS %s % partitions
table.kwargs['mysql_partitions'] = partitions
return ddl
m = MetaData()
t = Table('ti', m,
Column('id', Integer),
Column('amount', DECIMAL(7, 2)),
Column('tr_date', Date),
mysql_engine='InnoDB',
mysql_partition_by='HASH( MONTH(tr_date) )',
mysql_partitions='6'
)
e = create_engine(mysql://scott:tiger@localhost/test, echo=True)
t.create(e)
On Jul 26, 2013, at 7:00 AM, Michal Nowikowski godf...@gmail.com wrote:
Hello,
MySQL supports partitioning:
http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html
It requires special CREATE TABLE statement.
Is it possible to make use of this partitioning in SA?
How to do this?
Regards,
Godfryd
--
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] order_by column in adjoining table
you'd need to make a relationship() that explicitly joins out among all four
tables so that the .Time column is available. To accomplish that you'd
probably want to make a non primary mapper to join to, it would look something
like this:
sel = select([TrafficInput,
Traffic.Time]).select_from(join(TagCaseLink,
TrafficInput).join(TrafficDevice).join(Traffic)).alias()
m = mapper(TrafficInput, sel)
TagList.trafficInputs = relationship(m, order_by=sel.c.Time)
It's probably not how I'd go as it's too complicated. I'd either change the
schema to work more naturally (that you need to join across four tables to get
a certain ordering is a bit of a red flag) or possibly just order in memory,
assuming those relationships all tend to get loaded.
On Jul 26, 2013, at 3:26 AM, Andrew S andrew.suce...@gmail.com wrote:
Given the following classes/tables:
class TagList(Base):
TagID = Column(Integer, primary_key=True)
trafficInputs = relationship('TrafficInput', secondary='TagCaseLink',
order_by='Traffic.Time')
class TagCaseLink(Base):
TagID = Column(Integer, ForeignKey('TagList.TagID'), primary_key=True)
TrafficInputID = Column(Integer,
ForeignKey('TrafficInput.TrafficInputID'))
class TrafficInput(Base):
TrafficInputID = Column(Integer, primary_key=True)
TrafficDeviceID = Column(Integer,
ForeignKey('TrafficDevice.TrafficDeviceID'), nullable=False)
ConfigID = Column(Integer, ForeignKey('Config.ConfigID'), nullable=False)
class TrafficDevice(Base):
TrafficDeviceID = Column(Integer, primary_key=True)
TrafficID = Column(Integer, ForeignKey('Traffic.TrafficID'),
nullable=False)
DeviceID = Column(Integer, ForeignKey('Device.DeviceID'), nullable=False)
class Traffic(Base):
TrafficID = Column(Integer, primary_key=True)
TrafficName = Column(String, nullable=False)
Time = Column(DateTime, nullable=False)
I get the following error: sqlalchemy.exc.ProgrammingError:
(ProgrammingError) missing FROM-clause entry for table Traffic
So TagList contains a list of trafficInputs that I want ordered by
Traffic.Time. Is there anyway of doing this?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/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] order_by column in adjoining table
oh that should be mapper(TrafficInput, sel, non_primary=True)
On Jul 26, 2013, at 10:41 AM, Michael Bayer mike...@zzzcomputing.com wrote:
you'd need to make a relationship() that explicitly joins out among all four
tables so that the .Time column is available. To accomplish that you'd
probably want to make a non primary mapper to join to, it would look
something like this:
sel = select([TrafficInput,
Traffic.Time]).select_from(join(TagCaseLink,
TrafficInput).join(TrafficDevice).join(Traffic)).alias()
m = mapper(TrafficInput, sel)
TagList.trafficInputs = relationship(m, order_by=sel.c.Time)
It's probably not how I'd go as it's too complicated. I'd either change the
schema to work more naturally (that you need to join across four tables to
get a certain ordering is a bit of a red flag) or possibly just order in
memory, assuming those relationships all tend to get loaded.
On Jul 26, 2013, at 3:26 AM, Andrew S andrew.suce...@gmail.com wrote:
Given the following classes/tables:
class TagList(Base):
TagID = Column(Integer, primary_key=True)
trafficInputs = relationship('TrafficInput', secondary='TagCaseLink',
order_by='Traffic.Time')
class TagCaseLink(Base):
TagID = Column(Integer, ForeignKey('TagList.TagID'), primary_key=True)
TrafficInputID = Column(Integer,
ForeignKey('TrafficInput.TrafficInputID'))
class TrafficInput(Base):
TrafficInputID = Column(Integer, primary_key=True)
TrafficDeviceID = Column(Integer,
ForeignKey('TrafficDevice.TrafficDeviceID'), nullable=False)
ConfigID = Column(Integer, ForeignKey('Config.ConfigID'), nullable=False)
class TrafficDevice(Base):
TrafficDeviceID = Column(Integer, primary_key=True)
TrafficID = Column(Integer, ForeignKey('Traffic.TrafficID'),
nullable=False)
DeviceID = Column(Integer, ForeignKey('Device.DeviceID'), nullable=False)
class Traffic(Base):
TrafficID = Column(Integer, primary_key=True)
TrafficName = Column(String, nullable=False)
Time = Column(DateTime, nullable=False)
I get the following error: sqlalchemy.exc.ProgrammingError:
(ProgrammingError) missing FROM-clause entry for table Traffic
So TagList contains a list of trafficInputs that I want ordered by
Traffic.Time. Is there anyway of doing this?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/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.
--
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.
[sqlalchemy] query results are not converted to specialized class with joined table inheritance
hi all, i have a problem with joined table inheritance, which seems pretty difficult to boil down to a minimal test case; so I'm just throwing a prose description out, hoping someone encountered and solved this: When querying, the results are not converted to instances of the specialized class, although the sql query seems to be created correctly, i.e. including a LEFT OUTER JOIN clause. I don't really know how to go on with debugging, so all I did so far was comparing this particular case with others in my app where it works - without spotting any problems. any ideas? robert -- 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] sorry, too many clients already
On Thu, Jul 25, 2013 at 8:39 PM, kris kkvilek...@gmail.com wrote: On Thursday, July 25, 2013 4:12:50 PM UTC-7, Klauss wrote: On Thu, Jul 25, 2013 at 7:58 PM, kris kkvil...@gmail.com wrote: My postgres.conf has a parameter max_connections = 100 That's not only the default, but it's also not really recommended to push it much higher, so only do so if you really need a big pool on each machine, and if you're sure that pool will be mostly idle all of the time. Hmm.. I just bumped it 200 and modified the shared_buffers to be 32MB Perhaps a bit OT on this list, but you probably want to increase that further. Check out postgres' tuning wiki[0] [0] http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server -- 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] query results are not converted to specialized class with joined table inheritance
use echo=debug, then look at the column coming back which represents the discriminator. that value is what determines the type. On Jul 26, 2013, at 11:20 AM, Robert Forkel xrotw...@googlemail.com wrote: hi all, i have a problem with joined table inheritance, which seems pretty difficult to boil down to a minimal test case; so I'm just throwing a prose description out, hoping someone encountered and solved this: When querying, the results are not converted to instances of the specialized class, although the sql query seems to be created correctly, i.e. including a LEFT OUTER JOIN clause. I don't really know how to go on with debugging, so all I did so far was comparing this particular case with others in my app where it works - without spotting any problems. any ideas? robert -- 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] automated mapping - sqlalchemy or sqlsoup or something else
FYI: There's also sqlacodegen: https://pypi.python.org/pypi/sqlacodegen/ On Thu, Jul 25, 2013 at 11:29 AM, Michael Bayer mike...@zzzcomputing.comwrote: check out sqlautocode: https://code.google.com/p/sqlautocode/ On Jul 25, 2013, at 12:19 PM, Henning Sprang henning.spr...@gmail.com wrote: Hi, To automatically be able to access a large legacy database whithout having to write manual mapping code, I'd like to have a tool with automated mapping support. There seem to be two tools that offer something like this - sqlsoup and sqlasagna, but both seem not very actively supported - no commits since more than a year, sqlasagna has partly wrong documentation (saying its available in pypi, while it isn't, one example), so I'm not sure if I really should use one of them and if I'm not missing something. I am aware of the automated mapping functionality I can get with declarative_base and DeferredReflection, but then I still have to write classes for all tables plus define relationships. I might be able to script that myself (to make it work automated at runtime, or as a class generator as it is avialable in DjangoORM), but still, I'm asking if there is another tool I didn't find yet. Thanks, Henning -- 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. -- -- Kevin Horn -- 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] query results are not converted to specialized class with joined table inheritance
my bad, i switched from using orm to raw insert statements for initial population of one table of my database and forgot to insert a value for the discriminator. thanks for the hint. best robert On Fri, Jul 26, 2013 at 5:50 PM, Michael Bayer mike...@zzzcomputing.comwrote: use echo=debug, then look at the column coming back which represents the discriminator. that value is what determines the type. On Jul 26, 2013, at 11:20 AM, Robert Forkel xrotw...@googlemail.com wrote: hi all, i have a problem with joined table inheritance, which seems pretty difficult to boil down to a minimal test case; so I'm just throwing a prose description out, hoping someone encountered and solved this: When querying, the results are not converted to instances of the specialized class, although the sql query seems to be created correctly, i.e. including a LEFT OUTER JOIN clause. I don't really know how to go on with debugging, so all I did so far was comparing this particular case with others in my app where it works - without spotting any problems. any ideas? robert -- 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. -- 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.
[sqlalchemy] Mocking RowProxy and ResultProxy objects
Hello there, I''ve extended both RowProxy and ResultProxy, and I'm trying to figure out a way to mock objects of these classes in order to write some unit tests. I was looking at the SQLAlchemy's unittests and I'm still not sure how to do this without using a real database. Google hasn't been very helpful either. Any hints? :) Thanks! -- 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.
[sqlalchemy] mapper could not assemble primary key for table
I have following table:
class Group(DeclarativeBase):
Group definition
Only the ``group_name`` column is required.
__tablename__ = 'tg_group'
id = Column(Integer, autoincrement=True, primary_key=True)
group_name = Column(Unicode(16), unique=True, nullable=False)
display_name = Column(Unicode(255))
created = Column(DateTime, default=datetime.now)
users = relation('User', secondary=user_group_table, backref='groups')
'id' used to be 'group_id'. I am using TGII and just wrote a migration
script to change the 'group_id' field to 'id'. I then manually went into
class (above) and changed it to 'id' as well.
Now upon running my project, I get following error message:
sqlalchemy.exc.ArgumentError: Mapper Mapper|Group|tg_group could not
assemble any primary key columns for mapped table 'tg_group'
I am at a loss as to why this is happening. I changed all the references to
the 'group_id' field to 'id'. Googl'ing this error often comes with a
solution that some table does not have a primary key, but I do not see how
this could be the case here - as I clearly state 'primary_key = True' for
the 'id' field.
Can anyone please help me with the trouble shooting?
--
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.
