gmane.comp.python.sqlobject

Re: Select Single Random Record

Petr Jakeš — 2012-04-18T20:51:06

IMHO you have to always know which SQL engine is on the on the other side
(in other words you have to know how to set up the connection string).
If you know the engine, you can decide which kind of SQL "slang" to use:
http://www.petefreitag.com/item/466.cfm

Petr
------------------------------------------------------------------------------
Better than sec? Nothing is better than sec when it comes to
monitoring Big Data applications. Try Boundary one-second 
resolution app monitoring today. Free.
http://p.sf.net/sfu/Boundary-dev2dev_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss< at >lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: Select Single Random Record

Hans Georg Schaathun — 2012-04-18T20:14:44

In my case, yes; it is number crunching.
Question should be «is it /that/ "time critical"» :-)
I have had some difficulties with slow operations due
to multiple queries, and it feels more satisfactory get
rid of any avoidable query, even if the saving is only
a fraction of a percent of the total running time.


thousands in current tests;
hundreds of thousands is likely in future tests,
and millions is plausible.


Might be; especially when it is over the Internet as opposed to
over a LAN.  Yet, I am as concerned with query processing time than
transmission time.


Could vary.  About 50 clients querying once a minute each
may be typical, but I am sure I will see faster jobs completing
in seconds, in which case each client would query correspondingly
often.


postgres at the moment.


Ah.  Thanks for the tip.
I will look into it; obviously, I would prefer the solution to
be engine independent, but this feature might be more valuable :-)

:-- George

Re: Select Single Random Record

Petr Jakeš — 2012-04-18T19:43:23

Is your task really "time critical"?
How many records (IDs) do you expect to be returned using the first select?
How many kb will it take? Is the list size a real problem for your Internet
connection?
How often do you expect to release the select querry?

2)
what is your target database engine?
some databases allow "rand" in the select query.
see:
http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql

Petr
------------------------------------------------------------------------------
Better than sec? Nothing is better than sec when it comes to
monitoring Big Data applications. Try Boundary one-second 
resolution app monitoring today. Free.
http://p.sf.net/sfu/Boundary-dev2dev_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss< at >lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: Select Single Random Record

Hans Georg Schaathun — 2012-04-18T19:23:19

Although this might work in the particular application, but
it is hardly ideal to make two queries, nor to load a long list
of IDs.  Is there a way to do it with a single query?

Re: Select Single Random Record

Pʀoмᴇтнᴇυs 0x01 — 2012-04-18T19:14:57

I'll see about adding a recipe to the SQLObject Community Doc site for this.

(  https://sites.google.com/site/sqlobject/  )

On Wed, Apr 18, 2012 at 12:46 PM, Petr Jakeš <petr.jakes< at >tpc.cz> wrote:

------------------------------------------------------------------------------
Better than sec? Nothing is better than sec when it comes to
monitoring Big Data applications. Try Boundary one-second 
resolution app monitoring today. Free.
http://p.sf.net/sfu/Boundary-dev2dev_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss< at >lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: Select Single Random Record

Petr Jakeš — 2012-04-18T18:46:47

*Get the list of valid IDs from the remote database using the raw SQL
command
something like:*

con = connectionForURI("firebird://yourUserName:yourPassword< at >serverAddress
:3050/pat/to/database/database.file")

validIDs= "SELECT ID FROM SONGS_TABLE where ......"

returnedIDs = con.queryAll(validIDs)

# you get the list of valid IDs, something like this: [(1,), (2,), (3,),
(4,), (5,), (6,), (7,), (8,), (9,), (11,)]

*than:*

import random
randomID = random.choice(returnedIDs)[0]
randomSong = SongsTable.get(randomID)

HTH

Regards

Petr
------------------------------------------------------------------------------
Better than sec? Nothing is better than sec when it comes to
monitoring Big Data applications. Try Boundary one-second 
resolution app monitoring today. Free.
http://p.sf.net/sfu/Boundary-dev2dev_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss< at >lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: Select Single Random Record

Oleg Broytman — 2012-04-18T18:31:51

   Do it in two steps. First, count the number of objects that satisfy
your criteria:

count = MyTable.select(whereClause).count()

   Optimize your whereClause and indices. Generate an int in range
0..count-1 (see module `random`). And select the item:

row = MyTable.select(whereClause, orderBy=MyTable.q.id)[random_int]

   With this query SQLObject uses OFFSET and LIMIT=1 to select exactly
that row.


   Aha, you mark played songs in the DB, so your whereClause certainly
contains "if was not played yet" criteria. In this case selecting just
the first item without ordering the result set ought to be enough:

row = MyTable.select(whereClause)[0]

   You have to experiment.

Oleg.

Re: Select Single Random Record

Adam Berg — 2012-04-18T17:44:28

gmail.com> 

To give context to the issue, I am building my own icecast2 broadcaster. I want 
to have an Auto DJ feature which will pick a random song from the database which 
hasn't recently been played plus some other criteria. It really doesn't matter 
which record i get back from the database, I just need one at random.


------------------------------------------------------------------------------
Better than sec? Nothing is better than sec when it comes to
monitoring Big Data applications. Try Boundary one-second 
resolution app monitoring today. Free.
http://p.sf.net/sfu/Boundary-dev2dev

Re: Select Single Random Record

Adam Berg — 2012-04-18T17:18:41

wrote:

It should be a different record each time. 
I don't need a way to produce the same results again.



------------------------------------------------------------------------------
Better than sec? Nothing is better than sec when it comes to
monitoring Big Data applications. Try Boundary one-second 
resolution app monitoring today. Free.
http://p.sf.net/sfu/Boundary-dev2dev

Re: Select Single Random Record

Hans Georg Schaathun — 2012-04-18T16:53:54

Obviously, i cannot defined «random» in his context, but I have
a somewhat related problem.

To me «random» means that every record is equally likely as
the return of the next execution of the query, where «likely»
can be entirely subjective seen from the user's point of view.

In my context the issue is a queue of number crunching jobs,
where a number of clients (10-100; maybe more) will query
for the next parameter set to run the calculation for.
A client receiving a job will set a field to indicate that
it is taken, and transaction locking is used to avoid 
multiple clients snitching the same job at the same time.

Ideally, I would have liked a query to retrieve the first
unlocked record (it is useful but not necessary to order
them by timestamp).

Random selection, which makes it unlikely that a client will
have to wait for the lock would be a useful second best. 

Or maybe there is another ingenious approach which I have not
thought of.  Admittedly, this is a quick and dirty solution
which seems

Re: Select Single Random Record

Oleg Broytman — 2012-04-18T16:38:42

   Please define "random" in the context of your task. Do you want a
different record every time? Or some non-deterministic record that could
be the same every time is a possible solution?

Oleg.

Select Single Random Record

Adam Berg — 2012-04-18T16:30:10

What is the best way to select a single random record from a table and
falls within the "where" criteria i have defined? I don't want to return a
huge result set each time and then pick a random one, as the database is
located outside of my network and accessed over the internet.

connectionForURI is not defined

Glenn Rutkowski — 2012-04-13T16:01:31

Everything was running fine last night.  Came in this morning and my 
test system is acting weird.  I'm doing my best to find the answer but 
in the meantime, if someone knows what this is, I could use a bit of help.

Thanks,

Glenn


Today:
 >>> from sqlobject import *
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
   File "sqlobject.py", line 8, in <module>
     conn = connectionForURI(connection_string)
NameError: name 'connectionForURI' is not defined


System Setup:

Python 2.7.2 on Windows XP
Path = c:\python27;c:\python27\Scripts;C:\Python27\Lib\site-packages


Installed pymssql
Installed SQLObject 1.3.0




 >easy_install -U SQLObject
Searching for SQLObject
Reading http://pypi.python.org/simple/SQLObject/
Reading http://sqlobject.org
Reading http://sqlobject.org/
Reading http://sqlobject.org/devel/
Best match: SQLObject 1.3.0
Downloading 
http://pypi.python.org/packages/2.7/S/SQLObject/SQLObject-1.3.0-py2.7.egg#md5=42e649b0c1
08b75a1bec8f22e84270fa
Processing SQLObject-1.3

connectionForURI is not defined

Glenn Rutkowski — 2012-04-13T16:38:26


Nevermind.

I would share with you the reason, but I don't want to get banned from 
The Internet.  And yes.  I mean 'The Whole Internet'.

-Glenn




------------------------------------------------------------------------------
For Developers, A Lot Can Happen In A Second.
Boundary is the first to Know...and Tell You.
Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
http://p.sf.net/sfu/Boundary-d2dvs2

Re: SQLObject 1.3.0

Oleg Broytman — 2012-03-31T11:49:42

   With this release I stopped supporting branch 1.1.


   Many kudos to Petr Jakes!

Oleg.

SQLObject 1.3.0

Oleg Broytman — 2012-03-31T11:42:38

Hello!

I'm pleased to announce version 1.3.0, the first stable release of branch
1.3 of SQLObject.


What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).


Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

Download:
http://pypi.python.org/pypi/SQLObject/1.3.0

News and changes:
http://sqlobject.org/News.html


What's New
==========

Features & Interface
--------------------

* PostgresConnection performs translation of exceptions to standard
  SQLObject's hierarchy of exceptions.

* Major update of Firebird

Re: SqlObject Sqlmeta fromDatabase problem

Jeremy Hoyle — 2012-03-27T10:43:02

Ok.. thank you. I was under the impression that this function will add an
column automatically to my database after I included it in my class. I
might code this functionality into sqlmeta. I would find this to be very
useful for future changes.

Jeremy

On Tue, Mar 27, 2012 at 5:32 AM, Oleg Broytman <phd< at >phdru.name> wrote:

------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here 
http://p.sf.net/sfu/sfd2d-msazure_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss< at >lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: SqlObject Sqlmeta fromDatabase problem

Oleg Broytman — 2012-03-27T10:32:46

   You've got it in the reverse direction. fromDatase gets the list of
columns *from* database. If you want to add a column from python to
database you ought to use sqlmeta.addColumn() with changeSchema set to
True.

Oleg.

Re: SqlObject Sqlmeta fromDatabase problem

Jeremy Hoyle — 2012-03-27T10:27:45

I added a stringcol to the class Company(SQLObject) table but it did not
auto update my database with the column. I am not sure why it is not
updating. I check my SQL database with an external GUI. The database views
below should be updated automatically when adding a new StringCol entry in
the Company table. Correct?

*BEFORE:*
class Company(SQLObject):
class sqlmeta:
        fromDatabase = True
        lazyUpdate = False
        cacheValues = True
    Company_Name = StringCol(length = 256, default = None)
    Company_Address = StringCol(length = 256, default = None)
    Company_Phone = StringCol(length = 256, default = None)

[image: Inline image 1]

*AFTER:*
class Company(SQLObject):
class sqlmeta:
        fromDatabase = True
        lazyUpdate = False
        cacheValues = True
    Company_Name = StringCol(length = 256, default = None)
    Company_Address = StringCol(length = 256, default = None)
    Company_Phone = StringCol(length = 256, default = None)
    *Company_Number = StringCol(length = 256, def

Re: SqlObject Sqlmeta fromDatabase problem

Oleg Broytman — 2012-03-27T09:43:35

   I have problems understanding the sentence. Did you add a column to
your database and then restarted the program? The program didn't
discover the new column? You can test the list of columns by printing
Company.sqlmeta.columnList and Company.sqlmeta.columns.

Oleg.

Re: SqlObject Sqlmeta fromDatabase problem

Petr Jakeš — 2012-03-27T07:13:17

Hi Jeremy,
what about the Naming Style?
see: Changing the Naming Style <http://sqlobject.org/SQLObject.html#id56>
.... By default names in SQLObject are expected to be mixed case in Python
(like mixedCase), and underscore-separated in SQL (like mixed_case). This
applies to table and column names.

Regards

Petr
------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here 
http://p.sf.net/sfu/sfd2d-msazure_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss< at >lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss