sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Fail to create table with fields named 'intersect' or 'group'

[Oleg B. <ph...@ph...>]

On Sat, Apr 23, 2011 at 05:52:07PM -0600, Pгoм???тн?????s 0x01 wrote:
> Not sure if this is a bug or not, but it seems like I cannot create tables
> that have variables (columns) named after SQL keywords; could/should they be
> escaped in some way, or is this a limitation of SQLObject?

   Not a bug, rather a deficiency - SQLObject doesn't quote column
names. You can try to create a patch, at least for MySQL, but pleas
don't make entire SQLObject MySQL-oriented; i.e. you should quote
column names only if the current DB is MySQL.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Potential bug? Fail to create table with fields named 'intersect' or 'group'

[Sean D. <hal...@gm...>]

Don't do that.

On Sun, Apr 24, 2011 at 6:08 PM, Pгoмᴇтнᴇυs 0x01
<pro...@gm...>wrote:

> Hi Daniel,
>
> I understand your point, however (someone correct me if I am wrong) I
> thought that the table and column names can be any properly escaped
> alphanumeric string in any DBMS that conforms to standards. In MySQL, I
> could name columns using `<colname>` to escape the name, and avoid problems
> with keyword names.
>
> So I am still wondering if this can be done within SQLObject (or if it
> already is, and I am doing something wrong), or if there are any plans to do
> so in the future?
>
> Thanks again
>
>
> On Sat, Apr 23, 2011 at 11:40 PM, Daniel Fetchinson <
> fet...@go...> wrote:
>
>> > Not sure if this is a bug or not, but it seems like I cannot create
>> tables
>> > that have variables (columns) named after SQL keywords; could/should
>> they be
>> > escaped in some way, or is this a limitation of SQLObject?
>>
>> Why would you want to do such a thing? Is your backend allows for it?
>> If not, sqlobject is right to complain, if yes, but some other
>> supported backend does not allow for it then sqlobject is also right
>> to complain because you might want to switch in the future so is
>> protecting you.
>>
>> Cheers,
>> Daniel
>>
>>
>> --
>> Psss, psss, put it down! - http://www.cafepress.com/putitdown
>>
>>
>> ------------------------------------------------------------------------------
>> Fulfilling the Lean Software Promise
>> Lean software platforms are now widely adopted and the benefits have been
>> demonstrated beyond question. Learn why your peers are replacing JEE
>> containers with lightweight application servers - and what you can gain
>> from the move. http://p.sf.net/sfu/vmware-sfemails
>> _______________________________________________
>> sqlobject-discuss mailing list
>> sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>>
>
>
>
> ------------------------------------------------------------------------------
> Fulfilling the Lean Software Promise
> Lean software platforms are now widely adopted and the benefits have been
> demonstrated beyond question. Learn why your peers are replacing JEE
> containers with lightweight application servers - and what you can gain
> from the move. http://p.sf.net/sfu/vmware-sfemails
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
>

Re: [SQLObject] Potential bug? Fail to create table with fields named 'intersect' or 'group'

[Pгoмᴇтнᴇυs 0. <pro...@gm...>]

Hi Daniel,

I understand your point, however (someone correct me if I am wrong) I
thought that the table and column names can be any properly escaped
alphanumeric string in any DBMS that conforms to standards. In MySQL, I
could name columns using `<colname>` to escape the name, and avoid problems
with keyword names.

So I am still wondering if this can be done within SQLObject (or if it
already is, and I am doing something wrong), or if there are any plans to do
so in the future?

Thanks again

On Sat, Apr 23, 2011 at 11:40 PM, Daniel Fetchinson <
fet...@go...> wrote:

> > Not sure if this is a bug or not, but it seems like I cannot create
> tables
> > that have variables (columns) named after SQL keywords; could/should they
> be
> > escaped in some way, or is this a limitation of SQLObject?
>
> Why would you want to do such a thing? Is your backend allows for it?
> If not, sqlobject is right to complain, if yes, but some other
> supported backend does not allow for it then sqlobject is also right
> to complain because you might want to switch in the future so is
> protecting you.
>
> Cheers,
> Daniel
>
>
> --
> Psss, psss, put it down! - http://www.cafepress.com/putitdown
>
>
> ------------------------------------------------------------------------------
> Fulfilling the Lean Software Promise
> Lean software platforms are now widely adopted and the benefits have been
> demonstrated beyond question. Learn why your peers are replacing JEE
> containers with lightweight application servers - and what you can gain
> from the move. http://p.sf.net/sfu/vmware-sfemails
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

Re: [SQLObject] Potential bug? Fail to create table with fields named 'intersect' or 'group'

[Daniel F. <fet...@go...>]

> Not sure if this is a bug or not, but it seems like I cannot create tables
> that have variables (columns) named after SQL keywords; could/should they be
> escaped in some way, or is this a limitation of SQLObject?

Why would you want to do such a thing? Is your backend allows for it?
If not, sqlobject is right to complain, if yes, but some other
supported backend does not allow for it then sqlobject is also right
to complain because you might want to switch in the future so is
protecting you.

Cheers,
Daniel


-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

[SQLObject] Is it possible to override the *.by<AltID> method?

[Pгoмᴇтнᴇυs 0. <pro...@gm...>]

Hi all,

Another newbie question: is it possible to override the *.by<AltID> method?
I have a custom encoding for that column (it is in base64), and I can't call
it naturally unless I can override the method. Writing my own method doesn't
seem to work -- it doesn't get called at all!

Thanks!

[SQLObject] Potential bug? Fail to create table with fields named 'intersect' or 'group'

[Pгoмᴇтнᴇυs 0. <pro...@gm...>]

Hi all,

Not sure if this is a bug or not, but it seems like I cannot create tables
that have variables (columns) named after SQL keywords; could/should they be
escaped in some way, or is this a limitation of SQLObject?

Re: [SQLObject] SQL IN operator

[kosset <kos...@go...>]

W dniu 2011-04-20 23:01, Oleg Broytman pisze:
> On Thu, Apr 21, 2011 at 12:55:32AM +0400, Oleg Broytman wrote:
>> On Wed, Apr 20, 2011 at 10:45:05PM +0200, kosset wrote:
>>> SELECT * FROM downloaded WHERE mess_id IN (46,47,100)
>> from sqlbuilder import IN
> from sqlobject.sqlbuilder import IN
>> result = Downloaded.select(IN(Downloaded.q.mess_id, [46,47,100]))
> Oleg.
It works like a charm! Thank you!

kos

Re: [SQLObject] SQL IN operator

[Oleg B. <ph...@ph...>]

On Thu, Apr 21, 2011 at 12:55:32AM +0400, Oleg Broytman wrote:
> On Wed, Apr 20, 2011 at 10:45:05PM +0200, kosset wrote:
> > SELECT * FROM downloaded WHERE mess_id IN (46,47,100)
> 
> from sqlbuilder import IN

from sqlobject.sqlbuilder import IN

> result = Downloaded.select(IN(Downloaded.q.mess_id, [46,47,100]))

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] SQL IN operator

[Oleg B. <ph...@ph...>]

On Wed, Apr 20, 2011 at 10:45:05PM +0200, kosset wrote:
> SELECT * FROM downloaded WHERE mess_id IN (46,47,100)

from sqlbuilder import IN

result = Downloaded.select(IN(Downloaded.q.mess_id, [46,47,100]))

   SQLBuilder[1] is still underdocumented, sorry.

1. http://sqlobject.org/SQLBuilder.html

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] SQL IN operator

[kosset <kos...@go...>]

Hello,

I know this is a simple question, but I have no idea how to write this 
query:

SELECT * FROM downloaded WHERE mess_id IN (46,47,100)

in SQLObject. I mean that I don't know how to use SQL IN operator with 
SQLObject...

Could anyone help me with this :)?

kos

Re: [SQLObject] Strange error (ImportError: cannot import name parse_qsl)

[Pгoмᴇтнᴇυs 0. <pro...@gm...>]

Yup! That was exactly the problem; thanks!

On Sat, Apr 16, 2011 at 4:55 PM, Sean DiZazzo <hal...@gm...>wrote:

> Do you have a file called cgi.py in the local directory or somewhere on the
> module search path?
>
> On Sat, Apr 16, 2011 at 3:44 PM, Pгoмᴇтнᴇυs 0x01 <
> pro...@gm...> wrote:
>
>> Hi all,
>>
>> I am getting a strange error on my linux development server, in
>> particular, the log is:
>>
>> Traceback (most recent call last):
>>   File "/www/python/loader.py", line 145, in loadModule
>>     loaded = __import__(sModule);
>>   File "/www/web/kusanagi.local/www/lf/index.py", line 4, in <module>
>>     import db;
>>   File "/www/web/kusanagi.local/www/lf/db.py", line 7, in <module>
>>     from sqlobject import *;
>>   File "/usr/local/lib/python2.6/dist-packages/SQLObject-1.0.0-py2.6.egg/sqlobject/__init__.py", line 9, in <module>
>>     from main import *
>>   File "/usr/local/lib/python2.6/dist-packages/SQLObject-1.0.0-py2.6.egg/sqlobject/main.py", line 32, in <module>
>>     import dbconnection
>>   File "/usr/local/lib/python2.6/dist-packages/SQLObject-1.0.0-py2.6.egg/sqlobject/dbconnection.py", line 2, in <module>
>>     from cgi import parse_qsl
>> ImportError: cannot import name parse_qsl
>>
>> Any ideas would be appreciated!
>>
>> Thanks
>>
>>
>> ------------------------------------------------------------------------------
>> Benefiting from Server Virtualization: Beyond Initial Workload
>> Consolidation -- Increasing the use of server virtualization is a top
>> priority.Virtualization can reduce costs, simplify management, and improve
>> application availability and disaster protection. Learn more about
>> boosting
>> the value of server virtualization. http://p.sf.net/sfu/vmware-sfdev2dev
>> _______________________________________________
>> sqlobject-discuss mailing list
>> sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>>
>>
>

Re: [SQLObject] Strange error (ImportError: cannot import name parse_qsl)

[Sean D. <hal...@gm...>]

Do you have a file called cgi.py in the local directory or somewhere on the
module search path?

On Sat, Apr 16, 2011 at 3:44 PM, Pгoмᴇтнᴇυs 0x01
<pro...@gm...>wrote:

> Hi all,
>
> I am getting a strange error on my linux development server, in particular,
> the log is:
>
> Traceback (most recent call last):
>   File "/www/python/loader.py", line 145, in loadModule
>     loaded = __import__(sModule);
>   File "/www/web/kusanagi.local/www/lf/index.py", line 4, in <module>
>     import db;
>   File "/www/web/kusanagi.local/www/lf/db.py", line 7, in <module>
>     from sqlobject import *;
>   File "/usr/local/lib/python2.6/dist-packages/SQLObject-1.0.0-py2.6.egg/sqlobject/__init__.py", line 9, in <module>
>     from main import *
>   File "/usr/local/lib/python2.6/dist-packages/SQLObject-1.0.0-py2.6.egg/sqlobject/main.py", line 32, in <module>
>     import dbconnection
>   File "/usr/local/lib/python2.6/dist-packages/SQLObject-1.0.0-py2.6.egg/sqlobject/dbconnection.py", line 2, in <module>
>     from cgi import parse_qsl
> ImportError: cannot import name parse_qsl
>
> Any ideas would be appreciated!
>
> Thanks
>
>
> ------------------------------------------------------------------------------
> Benefiting from Server Virtualization: Beyond Initial Workload
> Consolidation -- Increasing the use of server virtualization is a top
> priority.Virtualization can reduce costs, simplify management, and improve
> application availability and disaster protection. Learn more about boosting
> the value of server virtualization. http://p.sf.net/sfu/vmware-sfdev2dev
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>
>

[SQLObject] Strange error (ImportError: cannot import name parse_qsl)

[Pгoмᴇтнᴇυs 0. <pro...@gm...>]

Hi all,

I am getting a strange error on my linux development server, in particular,
the log is:

Traceback (most recent call last):
  File "/www/python/loader.py", line 145, in loadModule
    loaded = __import__(sModule);
  File "/www/web/kusanagi.local/www/lf/index.py", line 4, in <module>
    import db;
  File "/www/web/kusanagi.local/www/lf/db.py", line 7, in <module>
    from sqlobject import *;
  File "/usr/local/lib/python2.6/dist-packages/SQLObject-1.0.0-py2.6.egg/sqlobject/__init__.py",
line 9, in <module>
    from main import *
  File "/usr/local/lib/python2.6/dist-packages/SQLObject-1.0.0-py2.6.egg/sqlobject/main.py",
line 32, in <module>
    import dbconnection
  File "/usr/local/lib/python2.6/dist-packages/SQLObject-1.0.0-py2.6.egg/sqlobject/dbconnection.py",
line 2, in <module>
    from cgi import parse_qsl
ImportError: cannot import name parse_qsl

Any ideas would be appreciated!

Thanks

Re: [SQLObject] Faster Loading of Data to SQLite3

[Oleg B. <ph...@ph...>]

On Sat, Apr 16, 2011 at 05:31:52PM -0400, Massimo wrote:
> I'm using SQLite3 and loading datafiles of about 50,000 lines. The script
> takes a long time to update, probably because it keeps hitting the hard disk
> with every insert/update command. How can I speed up this process?

   Don't use highest-level SQLObject API, use middle-level SQLBuilder:
http://sqlobject.org/FAQ.html#how-to-do-mass-insertion
http://sqlobject.org/SQLBuilder.html#insert
   Hope this helps.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Faster Loading of Data to SQLite3

[Massimo <lo....@gm...>]

I'm using SQLite3 and loading datafiles of about 50,000 lines. The script
takes a long time to update, probably because it keeps hitting the hard disk
with every insert/update command. How can I speed up this process?

Re: [SQLObject] Vacation

[Oleg B. <ph...@ph...>]

On Wed, Apr 13, 2011 at 10:50:53PM +0200, Daniel Fetchinson wrote:
> > Hi, all! I am slowly coming back.
> 
> Hope you had a great vacation!

   Thank you! A bit too short ;), but good enough.

> >    To add spice to my life my mother-in-law - she lives with us - broke
> > her leg.
> 
> Hope she'll get better soon!

   She wouldn't, I'm afraid - in her 83 she's quite bad. But that's
completely offtopic. And thank you again.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Vacation

[Daniel F. <fet...@go...>]

> Hi, all! I am slowly coming back.

Hope you had a great vacation!

>    On return I immediately dived into Debian 6.0 - I've postponed
> upgrade to after the vacation. Today I finished with my notebook,
> tomorrow I'm going to upgrade servers.
>
>    To add spice to my life my mother-in-law - she lives with us - broke
> her leg.

Hope she'll get better soon!

(The only thing this message contains is hope.)

Cheers,
Daniel


-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

Re: [SQLObject] DataBaseIndex with USING

[Gregor H. <gh...@gr...>]

Hello,

Am Wed, 13 Apr 2011 14:04:47 +0400
schrieb Oleg Broytman <ph...@ph...>:

> On Thu, Apr 07, 2011 at 01:53:14PM +0200, Gregor Horvath wrote:

> > Is such a patch welcomed?
> 
>    Certainly. Thank you in advance!
> 

OK I'll make it together with the next re factoring of my app.

All the best to your mom!

--
Greg

                 
 
                                     

Re: [SQLObject] DataBaseIndex with USING

[Oleg B. <ph...@ph...>]

Hello! Sorry for the later answer.

On Thu, Apr 07, 2011 at 01:53:14PM +0200, Gregor Horvath wrote:
> it doesn't seem to be possible to define an index like this:
> 
>  CREATE INDEX location_trgm_idx ON location USING gist (address
>  gist_trgm_ops);
> 
> http://www.postgresql.org/docs/current/static/pgtrgm.html
> 
> After a quick look at index.py it should be possible to patch it to
> accept a new parameter for example "using" and insert it into the sql
> creation in: 
> 
> def sqliteCreateIndexSQL(self, soClass):
> 	[...]
>         ret = 'CREATE %s %s_%s ON %s (%s)' % \
>               (uniqueOrIndex,
>                self.soClass.sqlmeta.table,
>                self.name,
>                self.soClass.sqlmeta.table,
>                ', '.join(spec))
>         return ret        
> 
> should get something like:
>  
> def sqliteCreateIndexSQL(self, soClass):
> 	[...]
>         ret = 'CREATE %s %s_%s ON %s %s (%s)' % \
>               (uniqueOrIndex,
>                self.soClass.sqlmeta.table,
>                self.name,
>                self.soClass.sqlmeta.table,
> 	       self.using,
>                ', '.join(spec)
> 	       )
>         return ret        
> 
> I don't know if this is a postgresql specific thing or not and should
> therefore only be inserted for postgresql.

   We can afford to put the burden of not defining self.using upon the
user. At least for beginning.

> Is such a patch welcomed?

   Certainly. Thank you in advance!

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] SQL Server Login Fails

[Oleg B. <ph...@ph...>]

Hello! Sorry for the later answer.

On Wed, Apr 06, 2011 at 12:31:11PM -0400, luis cota wrote:
> What is the appropriate way to connect to a SQL Server database using
> Windows Authentication? I've attempted using a connection string like this:
> 
> conn_string = mssql://HOSTNAME/Test
> 
> This results in:
> 
> adodbapi.adodbapi.DatabaseError: (-2147352567, 'Exception occurred.', (0,
> u'Micr
> osoft OLE DB Provider for SQL Server', u"Login failed for user 'None'.",
> None, 0
> , -2147217843), None)
> 
> 
> I know the DB server is running, I am able to connect to it using Elixir /
> SQL Alchemy ... Please help

   I'm afraid the burden to investigate is upon you. Can you look it up
how does SA handles Windows Authentication?

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Efficiently updating rows?

[Oleg B. <ph...@ph...>]

Hello! Sorry for the later answer.

On Sat, Apr 02, 2011 at 07:41:17PM +0000, Mark wrote:
> I've got 100,000 users running my app which stores their 
> data locally. I'm now releasing cell phone apps and need 
> to sync the data through a web server.
> 
> Each user would have on average 10,000 rows of

   Wow, that's the task!

> item:
>    string item_name
>    int  days_since_last_update
>    int  item_value
> 
> What will happen about 50 times a day is the server will
> receive a list of 5 items to update their values ( if current 
> day is greater than that row's days_since_last_update+2 ) ...
> 
> So given item names: A, B, C, D, E whats the most 
> efficient way to get those 5 items and then update their
>  values if the current_day is greater than that items 
> days_since_last_update? 

   I don't see any other way but SELECT and UPDATE.

> Also each row is actually about 200 bytes. Thats 2mb 
> per user or 200gb for the database? Do I need to be 
> concerned about performance of my MySQL database? 

   Yes, I think you should worry about big memory, a fast disk, buffers
and indices. May be a few fast disks - MySQL 5.0+ can do data
partitioning, but you have to devise a good partitioning schema.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Vacation

[Oleg B. <ph...@ph...>]

Hi, all! I am slowly coming back.

   On return I immediately dived into Debian 6.0 - I've postponed
upgrade to after the vacation. Today I finished with my notebook,
tomorrow I'm going to upgrade servers.

   To add spice to my life my mother-in-law - she lives with us - broke
her leg.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] DataBaseIndex with USING

[Gregor H. <gh...@gr...>]

Hi,

it doesn't seem to be possible to define an index like this:

 CREATE INDEX location_trgm_idx ON location USING gist (address
 gist_trgm_ops);

http://www.postgresql.org/docs/current/static/pgtrgm.html

After a quick look at index.py it should be possible to patch it to
accept a new parameter for example "using" and insert it into the sql
creation in: 

def sqliteCreateIndexSQL(self, soClass):
	[...]
        ret = 'CREATE %s %s_%s ON %s (%s)' % \
              (uniqueOrIndex,
               self.soClass.sqlmeta.table,
               self.name,
               self.soClass.sqlmeta.table,
               ', '.join(spec))
        return ret        

should get something like:
 
def sqliteCreateIndexSQL(self, soClass):
	[...]
        ret = 'CREATE %s %s_%s ON %s %s (%s)' % \
              (uniqueOrIndex,
               self.soClass.sqlmeta.table,
               self.name,
               self.soClass.sqlmeta.table,
	       self.using,
               ', '.join(spec)
	       )
        return ret        

I don't know if this is a postgresql specific thing or not and should
therefore only be inserted for postgresql.

Is such a patch welcomed?

--
Greg

                                     

[SQLObject] SQL Server Login Fails

[luis c. <lo....@gm...>]

What is the appropriate way to connect to a SQL Server database using
Windows Authentication? I've attempted using a connection string like this:

conn_string = mssql://HOSTNAME/Test

This results in:

adodbapi.adodbapi.DatabaseError: (-2147352567, 'Exception occurred.', (0,
u'Micr
osoft OLE DB Provider for SQL Server', u"Login failed for user 'None'.",
None, 0
, -2147217843), None)


I know the DB server is running, I am able to connect to it using Elixir /
SQL Alchemy ... Please help

[SQLObject] Efficiently updating rows?

[Mark <mar...@gm...>]

I've got 100,000 users running my app which stores their 
data locally. I'm now releasing cell phone apps and need 
to sync the data through a web server.

Each user would have on average 10,000 rows of

item:
   string item_name
   int  days_since_last_update
   int  item_value

What will happen about 50 times a day is the server will
receive a list of 5 items to update their values ( if current 
day is greater than that row's days_since_last_update+2 ) ...

So given item names: A, B, C, D, E whats the most 
efficient way to get those 5 items and then update their
 values if the current_day is greater than that items 
days_since_last_update? 

Also each row is actually about 200 bytes. Thats 2mb 
per user or 200gb for the database? Do I need to be 
concerned about performance of my MySQL database? 
The only other operation would be returning a query of 
all the rows for that user with days_since_last_update 
greater than some_value..