Re: [sqlalchemy] Re: Aliasing a constant within a recursive CTE
I now have a less klugey (no strings) implementation that I came to after
circling back to the the recursive CTE example in the docs, but is
structured in a way that I can wrap my head around a lot better:
import sqlalchemy as sa
#initialize a recursive CTE construct using the non-recursive term...
cte_init = sa.\
select([sa.literal(0).label(x), ]).\
cte(recursive = True, name = cte)
#Make an alias that can be thought of as the CTE's working table...
# - this is *mandatory* for SQLAlchemy to build the query correctly,
# even though the alias itself is not required in this particular
# resulting query (is it ever needed?)
cte_working = cte_init.alias(working)
cte_recurs = sa.\
select([cte_working.c.x + 1, ]).\
where(cte_working.c.x 10)
cte = cte_init.union_all(cte_recurs)
statement = sa.select([cte.c.x, ])
print statement
which yields:
WITH RECURSIVE cte(x) AS
(SELECT :param_1 AS x UNION ALL SELECT working.x + :x_1 AS anon_1
FROM cte AS working
WHERE working.x :x_2)
SELECT cte.x
FROM cte
statement.compile().params
{u'x_2': 10, u'param_1': 0, u'x_1': 1}
which works perfectly in PostgreSQL:
print conn.execute(statement).fetchall()
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
As per the comments in the code, the alias is absolutely required (it gets
mangled without the alias), but is seemingly pointless in the resulting
SQL. Of course, it wouldn't be that surprising if it were required for a
more complex CTE (or strict SQL compliance?), but I'm wondering if it is
just needed for the underlying query generator?
Anyway - with this more closely matching the example in the docs I wonder
what the heck my problem was originally.
That said, it might be useful to structure the documented CTE examples in a
way like I've done above (init, working, recursive, and final cte) as an
easier introduction to how to use CTEs in SQLAlchemy (at least for the
recursive ones). Hopefully it helps someone else.
Russ
--
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] help with hierarchy
Hello, I need some hints on how to build a query on a data structure which turned out to be a hierarchy. Please find the picture of the EER diagram attached. I want to get all note entities for a given project with their corresponding project, shots and assets. Something like: (project1, shot1, asset1, note1) (project1, shot1, asset1, note2) (project1, shot1, asset2, note1) No problem with joins. but I also want to get also: (project1, None, None, note1) (project1, Shot1, None, note1) How is this possible with one query? Cheers Sebastian -- Sebastian Elsner-pipeline td - r i s e | fx t: +49 30 20180300 sebast...@risefx.com www.risefx.com r i s e | fx GmbH Schlesische Strasse 28 Aufgang B, 10997 Berlin Geschäftsführer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB 106667 B -- 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. attachment: hierarchy.png
Re: [sqlalchemy] Problem with Association Object mapping
Seems I got the append wrong, the below works fine, thanks again for your
help.
due_con.due_user = c.user
contact.due_dates.append(due_con)
On Tue, Jul 10, 2012 at 8:46 AM, Jules Stevenson
droolz...@googlemail.comwrote:
Hi Michael,
Yes, I am doing this:
due_con = model.ArkContactDueDate()
due_con.modded =
arkUtils.process_date(field_dataList['duecontact'])
due_con.contact = contact
#todo: cannot append since it gives a duff default error message.
#have triple checked against docs and DB looks sound, so not sure..
c.user.due_contacts.append(due_con)
...but still get the error - is there anything else I can look into? Thank
you for getting back to me, the level of support you give is outstanding.
Jules
On Mon, Jul 9, 2012 at 11:40 PM, Michael Bayer
mike...@zzzcomputing.comwrote:
On Jul 9, 2012, at 6:08 PM, Jules Stevenson wrote:
Hi All,
I'm trying to put together an Association Object mapping within a pylons
app, but am getting the following error:
OperationalError: (OperationalError) (1364, Field 'user_id' doesn't have
a default value) 'INSERT INTO contact_duedate_user_association
(contact_id, modded) VALUES (%s, %s)' (209L, datetime.date(2012, 7, 18))
As far as I can tell I've followed the recomendations in the association
object help and am somehwta stumped as to what is causing the error. The
classes are non-declarative, and are as follows (and if the formatting
holds up the relevant bits should be in bold). Any help enormously
appreciated.
mapping looks fine, you'd need to ensure every ArkContactDueDate you add
to the Session is associated with a User also in that session.
single_parent=True should not be needed here.
contacts_table = sa.Table('contacts', meta.metadata,
sa.Column('id', sa.types.Integer, primary_key=True),
sa.Column('project_id', sa.types.Integer, sa.ForeignKey('projects.id
')),
sa.Column('client_id', sa.types.Integer, sa.ForeignKey('clients.id
')),
sa.Column('firstname',sa.types.String(length=255)),
sa.Column('lastname', sa.types.String(length=255)),
sa.Column('email1', sa.types.String(length=255)),
sa.Column('email2', sa.types.String(length=255)),
sa.Column('workphone', sa.types.String(length=255)),
sa.Column('mobile', sa.types.String(length=255)),
sa.Column('company', sa.types.String(length=255)),
sa.Column('category', sa.types.String(length=255)),
sa.Column('job_role', sa.types.String(length=255)),
sa.Column('mailer', sa.types.Boolean),
sa.Column('type', sa.types.Integer), # 0: contact, 1: press
sa.Column('modified', sa.types.DateTime),
)
class ArkContact(object):
def __init__(self):
self.category = 0
self.type = 0
self.modified = datetime.datetime.now()
*#due_contact_association
contact_due_contact = sa.Table('contact_duedate_user_association',
meta.metadata,
sa.Column('contact_id', sa.types.Integer, sa.ForeignKey('contacts.id'),
primary_key = True),
sa.Column('user_id', sa.types.Integer, sa.ForeignKey('users.id'),
primary_key = True),
sa.Column('modded', sa.types.Date)
)
class ArkContactDueDate(object):
def __init__(self):
pass*
*# ArkContactDueDate
orm.mapper(ArkContactDueDate, contact_due_contact, properties={
'contact': orm.relation(ArkContact, backref = 'due_dates')
})*
# ArkUser - users module
orm.mapper(ArkUser, users_table, properties={
'notes': orm.relation(ArkNote,
secondary=user_notes_table,
single_parent=True,
backref='user',
order_by=notes_table.c.date,
cascade=all, delete, delete-orphan),
'software': orm.relation(ArkSoftware,
secondary=user_software_table,
backref='users'),
'ratings': orm.relation(ArkUserSoftwareRating,
cascade=all, delete,
backref='user'),
'job': orm.relation(ArkJob,
backref='user'),
*'due_contacts': orm.relation(ArkContactDueDate,
backref = 'due_user',
single_parent=True)*
})
--
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.
--
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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy
Re: [sqlalchemy] Re: Aliasing a constant within a recursive CTE
On Jul 10, 2012, at 2:04 AM, Russell Warren wrote: As per the comments in the code, the alias is absolutely required (it gets mangled without the alias), but is seemingly pointless in the resulting SQL. Referring to PG's docs: http://www.postgresql.org/docs/8.4/static/queries-with.html when I wrote the CTE functionality, I designed it to suit the included_parts example, where you can see there is an alias to included_parts.This alias fits naturally into SQLAlchemy's system of aligning object identity with lexical identity, that is, to say x == 5, you need an x object that is unique in the query based on it's Python identity. But you're right, I didn't study the previous example in that doc, which refers to t without any alias. This usage is not nearly as natural, because t refers to the UNION ALL as a whole, including the two select statements, one of which was called t as well. So referring to both the inner and outer t at the same time doesn't work with the SQLA's usual approach. So some modifications to CTE are made in r079123b04dc6 (0.8) / ra742d1526e86 (0.7) such that CTEs are now rendered based on name-based logic only. A given CTE name will only be rendered as a full statement once, and precedence rules now ensure that the outermost CTE is the one that's rendered in all cases. Two entirely non-related CTEs with the same name now generates a CompileError. With this change, your original intuition: import sqlalchemy as sa #set up the non-recursive part of the query (sort of?)... cte = sa.select( [sa.literal(0).label(x), ] ).cte(name = cte, recursive = True) #bring in the recursive part (sort of?)... cte = cte.union_all( sa.select([cte.c.x + 1, ]).\ where(cte.c.x 10) ) #select from the resulting CTE... statement = sa.select([cte.c.x, ]) print statement works as you expect: WITH RECURSIVE cte(x) AS (SELECT :param_1 AS x UNION ALL SELECT cte.x + :x_1 AS anon_1 FROM cte WHERE cte.x :x_2) SELECT cte.x FROM cte the original output you were getting isn't all that confusing (any more than the whole CTE thing is in the first place) - it just rendered cte(x) twice, one for each version used in the query. -- 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.
Re: [sqlalchemy] Re: Aliasing a constant within a recursive CTE
On Tue, Jul 10, 2012 at 11:31 AM, Michael Bayer mike...@zzzcomputing.comwrote: So some modifications to CTE are made in r079123b04dc6 (0.8) / ra742d1526e86 (0.7) such that CTEs are now rendered based on name-based logic only. A given CTE name will only be rendered as a full statement once, and precedence rules now ensure that the outermost CTE is the one that's rendered in all cases. Two entirely non-related CTEs with the same name now generates a CompileError. Awesome. Thanks for the changes, Mike. I confirmed it works as advertised for 0.7.9 (but you knew that). Until 0.7.9 is released I'll just use the cte_working alias trick. Your fix makes it unnecessary, but it doesn't seem to do any harm and will still work later so I'm set. Thanks again. And yeah, I agree that CTE thing/syntax is more than a bit confusing in general! Incredibly useful, though. Russ -- 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.
Re: [sqlalchemy] fractional second percision- mysql
I have not made any changes, have only proposed some hypothetical changes for the 0.7 series. I don't have this newest version of MySQL installed, so I was asking you to test the workaround I gave and/or the patch, to ensure it solves all the problems fully. This testing would also establish that the MySQL DBAPI is properly receiving/returning the microseconds field. If the workarounds I gave solve your problem fully, then I can commit the patch to SQLAlchemy and resolve that we are doing all that's needed for fractional time support. I have tried implementing your suggestions. Unfortunately, your workaround did not solve my problem fully. I successfully created the FracTime type which extends Time and that shows a field for fractional seconds. However, when I try to insert values into the FracTime column, I am still unable to to specify the fractional seconds part. Even after modifying Time's process method in lib/sqlalchemy/dialects/mysql/base.py to accept microseconds, I was still unable to get it to work. To ensure that it was not a problem with sqlalchemy, I turned on logging to look at was being passed to the DBAPI. What I see is: ... INFO:sqlalchemy.engine.base.Engine.0x...a3d0:(4L, 7.48, 0.27, datetime.timedelta (0, 8, 26), 93, 0, 0, 62032, 0, 0, 19, 15573, 0) ... It looks like datetime.timedelta is holding the microseconds correctly (that is 0 minutes, 8 seconds and 26 microseconds). It looks to me that sqlalchemy is correctly passing the microsecond value onto the DBAPI. After your workaround, this seems to have confirmed that I am having a problem with my DBAPI's (which I think is MySQLdb) communication with the db. Please let me know if you have anymore ideas. Thank you for your suggestions. -James -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/OLtWHwRJzKMJ. 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.
Re: [sqlalchemy] fractional second percision- mysql
On Jul 10, 2012, at 2:08 PM, James wrote: I have not made any changes, have only proposed some hypothetical changes for the 0.7 series. I don't have this newest version of MySQL installed, so I was asking you to test the workaround I gave and/or the patch, to ensure it solves all the problems fully. This testing would also establish that the MySQL DBAPI is properly receiving/returning the microseconds field. If the workarounds I gave solve your problem fully, then I can commit the patch to SQLAlchemy and resolve that we are doing all that's needed for fractional time support. I have tried implementing your suggestions. Unfortunately, your workaround did not solve my problem fully. I successfully created the FracTime type which extends Time and that shows a field for fractional seconds. However, when I try to insert values into the FracTime column, I am still unable to to specify the fractional seconds part. Even after modifying Time's process method in lib/sqlalchemy/dialects/mysql/base.py to accept microseconds, I was still unable to get it to work. To ensure that it was not a problem with sqlalchemy, I turned on logging to look at was being passed to the DBAPI. What I see is: ... INFO:sqlalchemy.engine.base.Engine.0x...a3d0:(4L, 7.48, 0.27, datetime.timedelta (0, 8, 26), 93, 0, 0, 62032, 0, 0, 19, 15573, 0) ... It looks like datetime.timedelta is holding the microseconds correctly (that is 0 minutes, 8 seconds and 26 microseconds). It looks to me that sqlalchemy is correctly passing the microsecond value onto the DBAPI. After your workaround, this seems to have confirmed that I am having a problem with my DBAPI's (which I think is MySQLdb) communication with the db. Please let me know if you have anymore ideas. Thank you for your suggestions. Yeah, I kind of suspected MySQLdb might have problems here. You need to file a bug report with the DBAPI's bug tracker: http://sourceforge.net/tracker/?group_id=22307atid=374932 poke around there and see if someone's already requested this feature. -- 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.
