[Tutor] Python sqlite3 issue

[Alan Gauld]

On 22/10/14 18:38, Juan Christian wrote:

> def ini_db():
>      db.execute('''
>              CREATE TABLE IF NOT EXISTS TOPICS(
>                      ID INTEGER NOT NULL,
>                      URL VARCHAR NOT NULL,
>                      AUTHOR VARCHAR NOT NULL,
>                      MESSAGE VARCHAR NOT NULL
>                  );
>              ''')

So you no longer have a primary key. You are taking on management
of uniqueness yourself. That's OK provided you understand fully the 
consequences of that decision.

Incidentally you don't need the semi-colon inside the execute. It can 
only execute the entire string so if there's only one command you
don't need the terminator.

> def insert_db(_id, url, author, message):
>      db.execute("INSERT INTO TOPICS (ID, URL, AUTHOR, MESSAGE) VALUES
> (?, ?, ?, ?)", (_id, url, author, message))
>      db.commit()

Note that this makes no checks for unique ID so you put the onus
on the inserting code to provide a unique ID.

> def get_db(_id):
>      cursor = db.execute("SELECT ID, URL, AUTHOR, MESSAGE FROM TOPICS
> WHERE ID = ?", (_id,))
>      return cursor.fetchone()

What happens if there are multiple rows returned (non unique IDs)?
You only get the first (and SQL does not guarantee order so it might
be a different one each time...), is that sufficient?


> if __name__ == '__main__':
>      ini_db()
>
>      insert_db(12, 'abc.com <http://abc.com>', 'a', 'b')
>      insert_db(20, 'abc2.com <http://abc2.com>', 'a2', 'c')
>      insert_db(1, 'abc3.com <http://abc3.com>', 'a3', 'd')
>
>      for row in db.execute('SELECT * FROM TOPICS ORDER BY ID'):
>          print(row)
>
>      db.close()
>
>
> ------
>
> Anything else that I need to improve/change? Regarding the 'DROP TABLE'
> before creating, It wouldn't be good for me, because I do want the 'old'
> data from the table there, I don't want to drop them!

That's fine but if you ever try to change the structure of your table 
(other than adding a column to the end) you will have to recreate the 
table; which you won't be able to do without dropping it first. (You can 
rename the original however and then copy the data from it to the new 
table, before dropping the renamed version.)

Finally, in production code you should not use select *. Always provide 
the field names in the order you want them. That's because if somebody 
else adds columns or changes their order (not likely in SQLite but 
common in other DBs) your select will not be broken.

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.flickr.com/photos/alangauldphotos