Re: Emulating a sequence

Replace your %s with ? and things should work...

It will handle the quotes and stuff for you.

Thanks,

Greg Givler
Information Technology
Lockwood®
10 Valley Stream Parkway
Malvern, PA 19355
Phone: (484) 605-4826
Email: ggivler-zdgUFu1MqnQ@xxxxxxxxxxxxxxxx


                                                                                
                                                       
                      David Pratt                                               
                                                       
                      <fairwinds@eastli        To:       
ggivler-zdgUFu1MqnQ@xxxxxxxxxxxxxxxx                                            
                  
                      nk.ca>                   cc:       
pysqlite-Y+ulVh6ByXJeoWH0uzbU5w@xxxxxxxxxxxxxxxxxxxxx                           
                            
                                               Subject:  Re: [pysqlite] 
Emulating a sequence                                           
                      05/03/2006 10:58                                          
                                                       
                      AM                                                        
                                                       
                                                                                
                                                       
                                                                                
                                                       




Hi Greg. Hopefully Tim and Gerhard are reading since it would be good to

get some feedback on my string substitution here as well.

What I've come up with is to do something like this:

stmt = """
update sequence
set commit_seq = commit_seq + 1
where id=1;
insert into ztransaction (
tid, commit_order, username, description, extension)
values (%s, (select commit_seq from sequence where id=1), "%s", "%s",
"%s");
"""
cursor.executescript(stmt % (tid_int, user, desc, ext))

I figure excutescript should execute both statements in a single
transaction so it will be as safe way to go. The value will always be
correct without worrying that another connection picked off the next
value before another committed it.

I am concerned about the way I am handling the string substitutions in
"%s", "%s" portion of my statement though - to ensure they will be
properly escaped.

I can't be sure of what I will get in the username field (varchar), desc

field (text) and the ext field (bytea) binary string. I'll likely post a

couple of examples to get some feedback on best way to write these with
pysqlite so I am comfortable.

See the difference between:

cursor.executescript(stmt % (tid_int, user, desc, ext))
and in postgres, I would have been written as:

stmt = """
insert into ztransaction (
tid, commit_order, username, description, extension)
values (%s, (select nextval('commit_seq')), %s, %s, %s)
"""
cursor.execute(stmt, (tid_int, user, desc, ext))

and I don't have to worry about it. With pysqlite, it throws an error so

I had to quote these. This does not feel right to me.

Regards,
David

ggivler-zdgUFu1MqnQ@xxxxxxxxxxxxxxxx wrote:
> I agree about the sequence, the issue here is that the underlying
> database, SQLite just doesn't have a sequences, so you are sort of
> committed to rolling your own. It is somewhat the price we pay for
using
> SQLite. I use sqlite all the time for a plethora of simple tasks. The
> real question to me always becomes, when do you move to a different
> database, everything that makes Sqlite attractive, easy to use, pretty
> fast, and can easily be imbedded in an app, is also summed up in its
> name sqlite is not meant to be the replacement for your primary Oracle
> DBMS :)
>
> Thanks,
>
> Greg Givler
> Information Technology
> Lockwood®
> 10 Valley Stream Parkway
> Malvern, PA 19355
> Phone: (484) 605-4826
> Email: ggivler-zdgUFu1MqnQ@xxxxxxxxxxxxxxxx


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please call the help desk at ext 4850
______________________________________________________________________




______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________