Re: Emulating a sequence

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