Python and sqlite trouble

I can't show the data from database sqlite in python.

connection = sqlite3.connect('db')
connection.cursor().execute('CREATE TABLE IF NOT EXISTS users (     \
                        id TEXT,                                    \
                        name TEXT,                                  \
                        avatar TEXT                                 \
                )')

# In cycle:
query = 'INSERT INTO users VALUES ("' + str(开发者_如何学Cfriend.id) + '", "' + friend.name + '", "' + friend.avatar +'" )'
print query
connection.cursor().execute(query)
connection.commit()

# After cycle
print connection.cursor().fetchall()

Sample output of query variable:

INSERT INTO users VALUES ("111", "Some Name", "http://avatar/path" )

In result, fetchall returns empty tuple. Why?

---

UPD Forgotten code:

connection.cursor().execute('SELECT * FROM users')
connection.cursor().fetchall()

→ []

---

INSERT does not return data. To get the data back out, you'll have to issue a SELECT statement.

---

import sqlite3

con = sqlite3.connect("db")

con.execute("create table users(id, name, avatar)")

con.execute("insert into users(id, name, avatar) values (?, ?, ?)", (friend.id, friend.name, friend.avatar))
con.commit()

for row in con.execute("select * from users")
    print row

con.close()

---

Because the create table string as displayed is syntactically invalid Python, as is the insert into string.

---

Actually, the answer to your first question is: because you use different cursors.

connection.cursor() creates a new cursor in the connection you created before. fetchall() gives you the results of the query you executed before in that same cursor. I.e. what you did was this:

# After cycle
cursor1 = connection.cursor()
cursor1.execute('SELECT * FROM users')
cursor2 = connection.cursor()
cursor2.execute("")
cursor2.fetchall()

What you should have done was this:

# After cycle
cursor = connection.cursor()
cursor.execute('SELECT * FROM users')
print cursor.fetchall()