[sqlalchemy] Re: PostgreSQL Tsvector type
Hello Michael, Michael Bayer mike_mp at zzzcomputing.com writes: the TsVector type looks perfect! if you were to package that up for inclusion, it would likely subclass TypeEngine and you’d implement PGTypeCompiler.visit_TSVECTOR.we appear to have a little bit of “tsvector” code already (we implement the “match” operator as %s at at to_tsquery(%s)” ) but not the full type. I've implemented this as per your suggestions and sent you a pull request here https://bitbucket.org/zzzeek/sqlalchemy/pull-request/8/implements- tsvector-type-for-the/diff Let me know what you think. 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] PostgreSQL Tsvector type
Hello everyone, I've been trying to get the postgreSQL tsvector type work with SQLAlchemy without dropping into raw SQL. I was mostly interested in queries and the DDL to create the initial table. Through some trial and error, I have a setup working which I've detailed here http://nibrahim.net.in/2013/11/29/sqlalchemy_and_full_text_searching_in_postg resql.html My experience with SQLAlchemy is limited so if someone here has some feedback, I can polish this out and hopefully contribute it back to the postgresql dialect module. 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] Re: PostgreSQL Tsvector type
Michael Bayer mike_mp at zzzcomputing.com writes: the TsVector type looks perfect! if you were to package that up for inclusion, it would likely subclass TypeEngine and you’d implement PGTypeCompiler.visit_TSVECTOR.we appear to have a little bit of “tsvector” code already (we implement the “match” operator as %s at at to_tsquery(%s)” ) but not the full type. Thank you Michael. I'll try to polish this and send you a PR. I'm more comfortable with git than with hg. I presume you accept request on github too? -- 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] Re: Single row of a One to Many query
On Jul 24, 7:19 pm, Michael Bayer mike...@zzzcomputing.com wrote:
[..]
finding a list of objects and the most recent/highest/somethingest
related item requires joining to a subquery, where the subquery selects
the func.max(desiredfield) and GROUP BY's the columns that relate the rows
to the parent.
An example of that is in the ORM tutorial under Using Subqueries.
Replace func.count() with func.max() and you're on your way.
I'm sorry about such a late follow up. I ended up working on something
else and couldn't try this out.
I spent a while trying to crack this but couldn't.
My mappers are Client and Order and I first create the subquery like
so.
stmt = session.query(Order.table.c.client_id,func.max
(Order.table.c.date).label('latest_order')).group_by
(Order.table.c.date).subquery()
Which selects the order with the maximum date.
I then build my complete query like so
for client, lo in session.query(Client, stmt.c.latest_order).filter
(Client.id == 16983).join((stmt,Client.id ==
stmt.c.client_id)).order_by(Client.id): print client,lo
(The filter is there just to limit the clients to a single one)
I get back a all the orders for the client which seems to make sense
from the SQL standpoint but it's not what I want.
I get back something like
Client#16983 date0
Client#16983 date1
Client#16983 date2
which are the 3 orders that the client has placed.
What I want is the latest one only. ie.
Client#16983 date2
Can't I get just these?
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: Single row of a One to Many query
On Sep 3, 6:15 pm, Mike Conley mconl...@gmail.com wrote:
On Thu, Sep 3, 2009 at 8:05 AM, Noufal nou...@gmail.com wrote:
stmt = session.query(Order.table.c.client_id,func.max
(Order.table.c.date).label('latest_order')).group_by
(Order.table.c.date).subquery()
I think your group_by needs to be Order.table.c.client_id to get latest
order per client
Ah. Select the one with the highest date when they're bunched together
by client_id. Makes sense. It works too.
Thanks Mike.
--~--~-~--~~~---~--~~
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] Single row of a One to Many query
Hello everyone, I've been using sqlalchemy with elixir for a legacy project for a while now and recently needed to write some more than trivial queries. I have the default elixir generated mappers but using only them forces me to do some data processing in my app rather than in the database. This makes it slow. What I need to do is something like this. I have a table of clients which contains id, name and a couple of other fields. This is linked to an orders table through a OneToMany relationship (each client had multiple orders). Every row of the order table contains some details like date, type etc. and a backlink (foreign key) back to the clients table. I need to query out a list of clients and their latest orders. My mappers are not set up for this and so I need to do a manual query. Can someone suggest how I'd do this in sqlalchemy? 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] mySQL and timedelta
Hello everyone,
I'm having some trouble with a query involving a timedelta object
with a mySQL backend (MyISAM). I have a table called Run that has two
fields like so.
sa.Column('starttime', sa.TIMESTAMP),
sa.Column('endtime', sa.TIMESTAMP)
I'm trying to find all rows that have endtime - starttime equal to
some timedelta obtained from a user. The query is constructed like
this
l = datetime.timedelta(seconds=907)
cc = ((Run.c.endtime - Run.c.starttime) == l)
t=q.select(cc)
2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
SELECT runs.status AS runs_status, runs.workdir AS runs_workdir,
runs.machinetype AS runs_machinetype, runs.endtime AS runs_endtime,
runs.hostname AS runs_hostname, runs.cmdline AS runs_cmdline,
runs.incremental AS runs_incremental, runs.`user` AS runs_user,
runs.starttime AS runs_starttime, runs.rid AS runs_rid
FROM runs
WHERE (runs.endtime - runs.starttime) = %s ORDER BY runs.rid
2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
[datetime.timedelta(0, 907)]
The %s looks funny there.
This returns the wrong row and when I do this kind of query via my
script (which is web based), I get messages in the server error log
that look like this
/usr/lib/python2.5/site-packages/sqlalchemy/databases/mysql.py:313:
Warning: Truncated incorrect DOUBLE value: '00:08:40'
cursor.execute(statement, parameters)
I expect the users to input values like HH:MM:SS which I split and use
to create the timedelta object.
I'd appreciate any help .
Peace.
--~--~-~--~~~---~--~~
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: Auto load problem with SQLAlchemy 0.3.10 and mySQL
The MyISAM storage engine doesn't retain foreign key information- to
autoload FKs you must create the table with a storage engine that
supports FKs such as InnoDB. That looks like:
Table('stats', metadata, ..columns.., mysql_engine='InnodB')
You can also use ALTER TABLE via mysql to change the storage engine and
add foreign keys if you've already got these MyISAM tables in production
(see the mysql docs.)
Thanks Jason. The concept of which engine mySQL was using didn't come
into my head. i just used the default.
Supplying an explicit ForeignKeyConstraint clause for an autoloaded
MyISAM table is just fine too, if you want to keep using that storage
engine.
This is what I've done right now.
--~--~-~--~~~---~--~~
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: Auto load problem with SQLAlchemy 0.3.10 and mySQL
Did you originally create the tables through SQLAlchemy or are you reflecting an existing schema? In either case I'd need to see the output of SHOW CREATE TABLES for the problem table to make a diagnosis. I create the tables using SQLAlchemy. I'll send you the output in a day, I'm away from the machine where this code is right now. You can workaround this by manually adding a FK after autoloading: stats_table.append_constraint( sa.ForeignKeyConstraint(['rid'], ['runs.rid'])) I'll try that. Thanks for the help. --~--~-~--~~~---~--~~ 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: Auto load problem with SQLAlchemy 0.3.10 and mySQL
On Sep 20, 10:34 pm, Noufal [EMAIL PROTECTED] wrote: [..] I create the tables using SQLAlchemy. I'll send you the output in a day, I'm away from the machine where this code is right now. The output of the create table column looks like this CREATE TABLE `stats` ( `sid` int(11) NOT NULL auto_increment, `rid` int(11) default NULL, `stagename` varchar(50) default NULL, `description` varchar(250) default NULL, `starttime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `endtime` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`sid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
