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(friend.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()
→ []
asked Jul 31, 2010 at 16:45
Max Frai
64.7k82 gold badges206 silver badges311 bronze badges
4 Answers 4
INSERT does not return data. To get the data back out, you'll have to issue a SELECT statement.
answered Jul 31, 2010 at 16:49
Larry Lustig
51.2k16 gold badges119 silver badges173 bronze badges
Sign up to request clarification or add additional context in comments.
Comments
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()
answered Jul 31, 2010 at 17:05
Wok
5,4038 gold badges47 silver badges74 bronze badges
Comments
Because the create table string as displayed is syntactically invalid Python, as is the insert into string.
answered Jul 31, 2010 at 16:54
msw
43.7k9 gold badges92 silver badges118 bronze badges
2 Comments
Larry Lustig
I think he's referring to the quotes, although I'm guessing that the mismatch is not really present in your code since you don't get a Python syntax error, and since the INSERT statement prints out ok.
Max Frai
@larry-lustig @msw oh sorry, that's my fault. There is passing constant string and I forgot to take out the quotes after replacing it with simple 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()
Comments
default