[Tutor] Python sqlite3 issue
Joel Goldstick
joel.goldstick at gmail.com
Mon Oct 20 19:17:02 CEST 2014
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
More information about the Tutor
mailing list