pysqlite Question

wxPython Users
1

Josiah Carlson wrote:

  

I know this is not related to the wxPython list but I guess
I will get an answer quicker here than search through piles
of documentation.

How can I find the next available id from a sqlite database using pysqlite?
    
You shouldn't need to do that, sqlite supports auto-increment primary
key columns. But if you do, you can select the max from the column
and add one. It is not safe with respect to other things changing
data from underneath you, but it will work if you only have one
thread/process mucking with your data.

Beware, it implements auto increment poorly and arguably dangerously IMO. It just adds one to the last ID that exists, so if you remove the last row and then add another one, the new one will get the old row's ID (or earlier). So if your IDS are 1,2,3, and you remove 3 and add another row, it will get ID 3 (again). If they are 1,2,4 and you remove 4, a new row will get ID 3! (http://www.sqlite.org/faq.html#q1)

This will require you to be quite careful about removing all traces of references to rows by IDs, when a row is deleted. I have been bitten numerous times by this. Anyone know of a way to get actual unique IDs? I have been tempted to handle it myself, persisting the last row ID used somewhere and incrementing from that.

- Mike

···

On Thu, Jul 24, 2008 at 12:26 AM, Wesley Nitsckie > <wesleynitsckie@gmail.com> wrote:

2

Mike Rooney wrote:

Josiah Carlson wrote:

I know this is not related to the wxPython list but I guess
I will get an answer quicker here than search through piles
of documentation.

How can I find the next available id from a sqlite database using pysqlite?
    
You shouldn't need to do that, sqlite supports auto-increment primary
key columns. But if you do, you can select the max from the column
and add one. It is not safe with respect to other things changing
data from underneath you, but it will work if you only have one
thread/process mucking with your data.

Beware, it implements auto increment poorly and arguably dangerously IMO. It just adds one to the last ID that exists, so if you remove the last row and then add another one, the new one will get the old row's ID (or earlier). So if your IDS are 1,2,3, and you remove 3 and add another row, it will get ID 3 (again). If they are 1,2,4 and you remove 4, a new row will get ID 3! (SQLite Frequently Asked Questions)

This will require you to be quite careful about removing all traces of references to rows by IDs, when a row is deleted. I have been bitten numerous times by this. Anyone know of a way to get actual unique IDs? I have been tempted to handle it myself, persisting the last row ID used somewhere and incrementing from that.

- Mike

Actually it looks like, from SQLite Autoincrement, you might be able to do it properly if you use the AUTOINCREMENT keyword, instead of the default auto incrementing algorithm. I will have to look into this!

- Mike

···

On Thu, Jul 24, 2008 at 12:26 AM, Wesley Nitsckie >> <wesleynitsckie@gmail.com> wrote:

3

I use this:

class counter():

def init(self,start=None):

  if start == None:

     self.value

= -1

  else:

     self.value

= start-1

def next(self):

  self.value += 1

  return self.value

I need a counter to start somewhere
I say:

ID=counter()

I need a new id:

ID.next()

-Brian

Brian Fett

1280 Disc Dr

SHK224

Shakopee, MN 55379

Phone: (952)402-2595

Brian.D.Fett@seagate.com

Mike Rooney mxr@qvii.com

Sent by: wxpython-users-bounces+brian.d.fett=seagate.com@lists.wxwidgets.org

No Phone Info Available
07/24/2008 10:32 AM

Please respond to

wxpython-users@lists.wxwidgets.org

To

wxpython-users@lists.wxwidgets.org
cc

Subject

Re: [wxpython-users] pysqlite Question

`Josiah Carlson wrote:

I know this is not related to the wxPython list but I guess

I will get an answer quicker here than search through piles

of documentation.

How can I find the next available id from a sqlite database using
pysqlite?

You shouldn’t need to do that, sqlite supports auto-increment primary

key columns. But if you do, you can select the max from the
column

and add one. It is not safe with respect to other things changing

data from underneath you, but it will work if you only have one

thread/process mucking with your data.

Beware, it implements auto increment poorly and arguably dangerously

IMO. It just adds one to the last ID that exists, so if you remove the

last row and then add another one, the new one will get the old row’s ID

(or earlier). So if your IDS are 1,2,3, and you remove 3 and add another

row, it will get ID 3 (again). If they are 1,2,4 and you remove 4, a new

row will get ID 3! (SQLite Frequently Asked Questions)

This will require you to be quite careful about removing all traces of

references to rows by IDs, when a row is deleted. I have been bitten

numerous times by this. Anyone know of a way to get actual unique IDs?
I

have been tempted to handle it myself, persisting the last row ID used

somewhere and incrementing from that.

  • Mike
···

On Thu, Jul 24, 2008 at 12:26 AM, Wesley Nitsckie > wesleynitsckie@gmail.com wrote:

wxpython-users mailing list

wxpython-users@lists.wxwidgets.org

http://lists.wxwidgets.org/mailman/listinfo/wxpython-users

`

4

This is already available as itertools.count().

- Josiah

···

On Thu, Jul 24, 2008 at 11:28 AM, <brian.d.fett@seagate.com> wrote:

I use this:

class counter():
   def __init__(self,start=None):
      if start == None:
         self.value = -1
      else:
         self.value = start-1
   def next(self):
      self.value += 1
      return self.value

I need a counter to start somewhere I say:
ID=counter(<int>)

I need a new id:
ID.next()

-Brian

Brian Fett
1280 Disc Dr
SHK224
Shakopee, MN 55379

Phone: (952)402-2595
Brian.D.Fett@seagate.com

Mike Rooney <mxr@qvii.com>
Sent by: wxpython-users-bounces+brian.d.fett=seagate.com@lists.wxwidgets.org
No Phone Info Available

07/24/2008 10:32 AM

Please respond to
wxpython-users@lists.wxwidgets.org
To
wxpython-users@lists.wxwidgets.org
cc
Subject
Re: [wxpython-users] pysqlite Question

Josiah Carlson wrote:

On Thu, Jul 24, 2008 at 12:26 AM, Wesley Nitsckie >> <wesleynitsckie@gmail.com> wrote:

I know this is not related to the wxPython list but I guess
I will get an answer quicker here than search through piles
of documentation.

How can I find the next available id from a sqlite database using
pysqlite?

You shouldn't need to do that, sqlite supports auto-increment primary
key columns. But if you do, you can select the max from the column
and add one. It is not safe with respect to other things changing
data from underneath you, but it will work if you only have one
thread/process mucking with your data.

Beware, it implements auto increment poorly and arguably dangerously
IMO. It just adds one to the last ID that exists, so if you remove the
last row and then add another one, the new one will get the old row's ID
(or earlier). So if your IDS are 1,2,3, and you remove 3 and add another
row, it will get ID 3 (again). If they are 1,2,4 and you remove 4, a new
row will get ID 3! (SQLite Frequently Asked Questions)

This will require you to be quite careful about removing all traces of
references to rows by IDs, when a row is deleted. I have been bitten
numerous times by this. Anyone know of a way to get actual unique IDs? I
have been tempted to handle it myself, persisting the last row ID used
somewhere and incrementing from that.

- Mike
_______________________________________________
wxpython-users mailing list
wxpython-users@lists.wxwidgets.org
http://lists.wxwidgets.org/mailman/listinfo/wxpython-users

_______________________________________________
wxpython-users mailing list
wxpython-users@lists.wxwidgets.org
http://lists.wxwidgets.org/mailman/listinfo/wxpython-users

5

Huh, good call, I guess I hadn’t found
that (I figured there should be a standard one).

The one problem with this module as
I see it is: with mine I can call id.value and get the current value, the
itertools version seems to lack any way to get this value (I even tried
int(i); int(str(id)[6:-1]) is a bit cumbersome and counter intuitive).
I suppose I could just say x=id.next() and then use x multiple times, but
I would prefer not to. Then again, for the original posters use, this would
be perfect.

Does anyone know where I should go to
suggest that int(itertools.count()) return the current value of the counter?
If this were changed I would definitely use it.

-Brian

Brian Fett

1280 Disc Dr

SHK224

Shakopee, MN 55379

Phone: (952)402-2595

Brian.D.Fett@seagate.com

“Josiah Carlson”
josiah.carlson@gmail.com

Sent by: wxpython-users-bounces@lists.wxwidgets.org

No Phone Info Available
07/24/2008 02:06 PM

Please respond to

wxpython-users@lists.wxwidgets.org

To

wxpython-users@lists.wxwidgets.org
cc

Subject

Re: [wxpython-users] pysqlite Question

`This is already available as itertools.count().

  • Josiah

I use this:

class counter():

def init(self,start=None):

  if start == None:

     self.value = -1

  else:

     self.value = start-1

def next(self):

  self.value += 1

  return self.value

I need a counter to start somewhere I say:

ID=counter()

I need a new id:

ID.next()

-Brian

Brian Fett

1280 Disc Dr

SHK224

Shakopee, MN 55379

Phone: (952)402-2595

Brian.D.Fett@seagate.com

Mike Rooney mxr@qvii.com

Sent by: wxpython-users-bounces+brian.d.fett=seagate.com@lists.wxwidgets.org

No Phone Info Available

07/24/2008 10:32 AM

Please respond to

wxpython-users@lists.wxwidgets.org

To

wxpython-users@lists.wxwidgets.org

cc

Subject

Re: [wxpython-users] pysqlite Question

Josiah Carlson wrote:

I know this is not related to the wxPython list but I guess

I will get an answer quicker here than search through piles

of documentation.

How can I find the next available id from a sqlite database
using

pysqlite?

You shouldn’t need to do that, sqlite supports auto-increment
primary

key columns. But if you do, you can select the max from
the column

and add one. It is not safe with respect to other things
changing

data from underneath you, but it will work if you only have one

thread/process mucking with your data.

Beware, it implements auto increment poorly and arguably dangerously

IMO. It just adds one to the last ID that exists, so if you remove
the

last row and then add another one, the new one will get the old row’s
ID

(or earlier). So if your IDS are 1,2,3, and you remove 3 and add another

row, it will get ID 3 (again). If they are 1,2,4 and you remove 4,
a new

row will get ID 3! (SQLite Frequently Asked Questions)

This will require you to be quite careful about removing all traces
of

references to rows by IDs, when a row is deleted. I have been bitten

numerous times by this. Anyone know of a way to get actual unique
IDs? I

have been tempted to handle it myself, persisting the last row ID
used

···

On Thu, Jul 24, 2008 at 11:28 AM, brian.d.fett@seagate.com wrote:

On Thu, Jul 24, 2008 at 12:26 AM, Wesley Nitsckie >> wesleynitsckie@gmail.com wrote:

somewhere and incrementing from that.

  • Mike

wxpython-users mailing list

wxpython-users@lists.wxwidgets.org

http://lists.wxwidgets.org/mailman/listinfo/wxpython-users

wxpython-users mailing list

wxpython-users@lists.wxwidgets.org

http://lists.wxwidgets.org/mailman/listinfo/wxpython-users

wxpython-users mailing list

wxpython-users@lists.wxwidgets.org

http://lists.wxwidgets.org/mailman/listinfo/wxpython-users

`

6

You should first email python-list@python.org . If the feedback there
is good, offer it up on python-dev@python.org . Note that the
earliest it could get in is Python 2.7/3.1, which won't happen for
another 1.5-2 years, because 2.6/3.0 are on feature freeze for release
this October.

- Josiah

···

On Fri, Jul 25, 2008 at 5:32 AM, <brian.d.fett@seagate.com> wrote:

Huh, good call, I guess I hadn't found that (I figured there should be a
standard one).

The one problem with this module as I see it is: with mine I can call
id.value and get the current value, the itertools version seems to lack any
way to get this value (I even tried int(i); int(str(id)[6:-1]) is a bit
cumbersome and counter intuitive). I suppose I could just say x=id.next()
and then use x multiple times, but I would prefer not to. Then again, for
the original posters use, this would be perfect.

Does anyone know where I should go to suggest that int(itertools.count())
return the current value of the counter? If this were changed I would
definitely use it.