Convert string to command..

[Abandoned]

Re: Convert string to command..

On Oct 18, 8:53 pm, Hrvoje Niksic <hnik...@xemacs .orgwrote:

Abandoned <best...@gmail. comwrites:

When you load it, convert the string to dict with cPickle.loads
instead of with eval.

>

Yes i understand and this very very good ;)

>
Good! :-)
>

psycopg2.Progra mmingError: invalid byte sequence for encoding "UTF8":
0x80
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encodin g".

>
Use a different column type for cache2's column, one more appropriate
for storing binary characters (perhaps BYTEA for Postgres). Don't
forget to also use a bind variable, something like:
>
cursor.execute( "INSERT INTO cache2 VALUES (?)", a)
>
Using "INSERT ... ('%s')" % (a) won't work, since the huge binary
string in a can contain arbitrary characters, including the single
quote.

I tryed:
cursor.execute( "INSERT INTO cache2 VALUES (?)", a)
and
cursor.execute( "INSERT INTO cache2 VALUES (%s)", (a,) )
but the result is same..

psycopg2.Progra mmingError: invalid byte sequence for encoding "UTF8":
0x80
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encodin g".

[Hrvoje Niksic]

Re: Convert string to command..

Abandoned <besturk@gmail. comwrites:

>Use a different column type for cache2's column, one more appropriate
>for storing binary characters (perhaps BYTEA for Postgres). Don't
>forget to also use a bind variable, something like:
>>
>cursor.execute ("INSERT INTO cache2 VALUES (?)", a)
>>
>Using "INSERT ... ('%s')" % (a) won't work, since the huge binary
>string in a can contain arbitrary characters, including the single
>quote.

>
I tryed:
cursor.execute( "INSERT INTO cache2 VALUES (?)", a)

Why are you ignoring the first sentence: "Use a different column type
for cache2's column, ..."? The use of bind variables in INSERT will
work only *after* you do the rest of the work.

[Peter Otten]

Re: Convert string to command..

Abandoned wrote:

I'm very confused :(
I try to explain main problem...

That's always a good first step; try to remember that when you start
your next thread.

I have a table like this:
id-1 | id-2 | value
23 24 34
56 68 66
56 98 32455
55 62 655
56 28 123
.... ( 3 millions elements)
>
I select where id=56 and 100.000 rows are selecting but this took 2
second. (very big for my project)
I try cache to speed up this select operation..
And create a cache table:
id-1 | all
56 {68:66, 98:32455, 62:655}
>
When i select where id 56 i select 1 row and its took 0.09 second but
i must convert text to dictionary..

Before you go on with your odd caching schemes -- is the database properly
indexed? Something like

CREATE UNIQUE INDEX mytable_id1_id2 ON mytable (id-1, id-2);

(actual syntax may differ) might speed up the lookup operation
enough that you can do without caching.

Peter

[Bruno Desthuilliers]

Re: Convert string to command..

sjdevnull@yahoo .com a écrit :

On Oct 18, 1:38 pm, Bruno Desthuilliers <bruno.
42.desthuilli.. .@wtf.websitebu ro.oops.comwrot e:

>Abandoned a écrit :
>(snip)
>>

>>I'm very confused :(
>>I try to explain main problem...
>>I have a table like this:
>>id-1 | id-2 | value
>>23 24 34
>>56 68 66
>>56 98 32455
>>55 62 655
>>56 28 123
>>.... ( 3 millions elements)
>>I select where id=56 and 100.000 rows are selecting but this took 2
>>second. (very big for my project)

>Not to bad in the absolute.
>>

>>I try cache to speed up this select operation..
>>And create a cache table:
>>id-1 | all
>>56 {68:66, 98:32455, 62:655}

>I really doubt this is the right way to go.
>>

>>When i select where id 56 i select 1 row and its took 0.09 second but
>>i must convert text to dictionary..
>>Have you got any idea what can i do this conver operation ?

>Other alread answered
>>

>>Have you got any idea what can i do cache for this table ?

>Depends on your RDBMS. And as far as I'm concerned, I'd start by trying
>to find out how to optimize this query within the RDBMS - good ones are
>usually highly optimized softwares with provision for quite a lot of
>performance tuning.

>
Just the overhead of the query is a killer compared to a dictionary
lookup in Python, even if all you're doing is selecting an integer
from a 1-row, 1-column table.

Indeed. But then why use a RDBMS at all ? Please understand that I'm not
saying that a RDBMS will beat a plain dict lookup not that a RDBMS will
solve world's problem, but that storing pickled Python's dicts into a
RDBMS is certainly not the best thing to do. It will *still* have the db
connection overhead anyway, and will be a nightmare to maintain in sync
with the real state of the db. Which is why I suggest *first* looking
for RDBMS-side tuning and optimization - which may include third-part
cache systems FWIW.

[Bruno Desthuilliers]

Re: Convert string to command..

Peter Otten a écrit :
(snip)

Before you go on with your odd caching schemes -- is the database properly
indexed? Something like
>
CREATE UNIQUE INDEX mytable_id1_id2 ON mytable (id-1, id-2);
>
(actual syntax may differ) might speed up the lookup operation
enough that you can do without caching.

Im my arms(tm) ! At least some sensible advice...

[Hrvoje Niksic]

Re: Convert string to command..

Hrvoje Niksic <hniksic@xemacs .orgwrites:

If you're generating the string from Python, use cPickle instead.
Much faster:

[...]

>>>t0 = time.time(); d2 = eval(s); t1 = time.time(); t1-t0

1.5457899570465 088

>>>t0 = time.time(); d2 = pickle.loads(s) ; t1 = time.time(); t1-t0

0.0603079795837 40234

It just occurred to me, for simple data structures like the ones we're
discussing here (dicts of ints), marshal should also be considered.
marshal is the module used for generating and loading .pyc files and,
while it doesn't support all the bells and whistles of pickle, it's
very fast:

>>t0 = time.time(); d2 = marshal.loads(s ); t1 = time.time(); t1-t0

0.0297288894653 32031

Benchmarks made with the timeit module confirm this difference.

Marshal has the added advantage of using much less space than the
(binary) pickle -- the example dictionary provided above pickled to a
string of 2667791 bytes, while marshal produced a string of 1700002
bytes.