[Tutor] Python sqlite3 issue

[Joel Goldstick]

On Mon, Oct 20, 2014 at 1:04 PM, Juan Christian
<juan0christian at gmail.com> wrote:
> I have this code (http://pastebin.com/Q21vQdHZ):
>
> import sqlite3
>
> db = sqlite3.connect('db.sqlite')
>
>
> def create_db():
>     db.execute('''
>     CREATE TABLE TOPICS(
>     ID INT PRIMARY KEY NOT NULL,
>     URL VARCHAR NOT NULL,
>     AUTHOR VARCHAR NOT NULL,
>     MESSAGE VARCHAR NOT NULL
>     );
>     ''')
>
>
> def insert_db(_id, url, author, message):
>     db.execute("INSERT INTO TOPICS (ID, URL, AUTHOR, MESSAGE) VALUES ({},
> {}, {}, {})".format(_id, url, author, message))

The above line looks suspect.  I rewrote like so:
>>> def insert_db(_id, url, author, message):
           print ("INSERT INTO TOPICS (ID, URL, AUTHOR, MESSAGE)
VALUES ({}, {}, {}, {})".format(_id, url, author, message))
...
>>> insert_db(12, "abc.com", "author", "message")
INSERT INTO TOPICS (ID, URL, AUTHOR, MESSAGE) VALUES (12, abc.com,
author, message)
>>>

I've never used format like that.  It looks like you need to quote the
strings.  I don't know if you can tell format to do that or if you
have to escape them.
>     db.commit()
>
>
> def get_db(_id):
> cursor = db.execute("SELECT ID, URL, AUTHOR, MESSAGE FROM TOPICS WHERE ID =
> {}".format(_id))
> return cursor.fetchone()
>
>
> if __name__ == '__main__':
> create_db()
> insert_db(12, 'abc.com', 'a', 'b')
> get_db(12)
> db.close()
>
> And when I try to execute it I get:
>
> First time executing:
>
> Traceback (most recent call last):
>   File ".\sql.py", line 29, in <module>
>     insert_db(12, 'abc.com', 'a', 'b')
>   File ".\sql.py", line 18, in insert_db
>     db.execute("INSERT INTO TOPICS (ID, URL, AUTHOR, MESSAGE) VALUES ({},
> {}, {}, {})".format(_id, url, author, message)
> )
> sqlite3.OperationalError: no such column: abc.com
>
>
> Second time executing:
>
> Traceback (most recent call last):
>   File ".\sql.py", line 28, in <module>
>     create_db()
>   File ".\sql.py", line 14, in create_db
>     ''')
> sqlite3.OperationalError: table TOPICS already exists

This makes sense since the create sql code created TABLE the first time
>
>
> What's the problem? It's just a test script just to learn sqlite3 with
> python.
>
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> https://mail.python.org/mailman/listinfo/tutor
>



-- 
Joel Goldstick
http://joelgoldstick.com