[Tutor] Python sqlite3 issue

[Juan Christian]

On Wed, Oct 22, 2014 at 4:37 PM, Alan Gauld <alan.gauld at 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?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20141022/2d203083/attachment-0001.html>