0

I am making a small script that, among other things, makes a database with four entries called id, x, y and z. Ids should be unique and sequential, so I'm using the "primary key" id for every entry. The other values can be any real numbers, but the database should not contain two entries for which all three x, y and z are the same.

So the table is defined like this:

 if __name__ == "__main__":
    c.execute('drop table nodes')
    c.execute('create table nodes (id integer primary key, x real, y real, z real)')

Then I insert new entries into the table as:

c.execute('select * from nodes where x={x} and y={y} and z={z}'.format(x=x,y=y,z=z))
            if len(c.fetchall())==0:
                q="""
                  INSERT INTO nodes
                  (`x`, `y`, `z`)
                  VALUES
                  (%f,%f,%f) 
                  """ % (x,y,z)
                c.execute(q)

This sort of works, but it gets very slow as the database grows bigger. Is there a faster way to insert entries into the database while making sure they are an unique combination of values?

ambidexter2017
  • 13
  • 1
  • 1
    Why don't you simply add a Unique Constraint to the table (over your three columns x, y, z), then simply insert and catch the exception? – Mike Scotty Aug 22 '17 at 11:37
  • Try this: https://stackoverflow.com/questions/5616895/how-do-i-use-prepared-statements-for-inserting-multiple-records-in-sqlite-using – damisan Aug 22 '17 at 11:39

1 Answers1

1

a primary key is just a way of making sure the table rows are unique, and if x,y,z are your only other columns, so why not just make them the PK?

If you create your table with x,y and z as the primary key:

CREATE TABLE nodes(
  x, 
  y, 
  z, 
  PRIMARY KEY (x,y,z)
);

then sqlite will not accept rows where they are all the same.

You can then make sure only unique rows are entered without having to handle the errors, by using REPLACE instead of INSERT: https://sqlite.org/lang_replace.html

REPLACE INTO nodes
    (`x`, `y`, `z`)
VALUES
    (%f,%f,%f)

And this should solve it.

greg_data
  • 2,247
  • 13
  • 20