On Wed, Oct 22, 2014 at 4:37 PM, Alan Gauld <alan.ga...@btinternet.com> wrote: > > 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.
Ok, thanks! Note that this makes no checks for unique ID so you put the onus > on the inserting code to provide a unique ID. > > 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? > I'll get this id from here (http://steamcommunity.com/app/440/tradingforum/), every topic has a unique ID. 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.) > I won't need to change the structure, but if I do, I can DROP the table, no problem with that. 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. Ok, thanks! NEW CODE: import sqlite3 db = sqlite3.connect('db.sqlite') 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 )''' ) def insert(topic_id, url, author, message): db.execute("INSERT INTO TOPICS (ID, URL, AUTHOR, MESSAGE) VALUES (?, ?, ?, ?)", (topic_id, url, author, message)) db.commit() def get(topic_id): cursor = db.execute("SELECT ID, URL, AUTHOR, MESSAGE FROM TOPICS WHERE ID = ?", (topic_id,)) return cursor.fetchone() if __name__ == '__main__': ini_db() insert(12, 'abc.com', 'a', 'b') insert(20, 'abc2.com', 'a2', 'c') insert(1, 'abc3.com', 'a3', 'd') for row in db.execute('SELECT ID, URL, AUTHOR, MESSAGE FROM TOPICS'): print(row) db.close() The only thing left now is that the topics in this forum has a one/two weeks lifespan, and I think Steam reuses the same ID for new topics that was used in, lets say a 1 month-old topic (already closed and gone for weeks), I don't know for sure, but their Inventory/User API is a mess in some parts, so I don't trust them in this matter either. How would be a good approach regarding that? Use UPDATE? Use if-else?
_______________________________________________ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
