sqlite3 cursor.fetchall() returns an empty array

Question

I'm ssh'd into a remote server, and there I created two python files as a test: one to create a database, and one to read data from it.

The script that creates the database

import os
import sqlite3

# set up database
conn = sqlite3.connect('Materials.db')
c = conn.cursor()

def createTable():
        c.execute("DROP TABLE IF EXISTS images")
        c.execute("CREATE TABLE images(ID TEXT, url TEXT)")
createTable()

path = os.getcwd()

imagepath = "/home/rootadmin/1080_images"
imagedir = os.listdir(imagepath)
for image in range(0,len(imagedir)):
        c.execute('INSERT INTO images(ID, url) VALUES(?,?)',(imagedir[image],'www.google.com'))
        print(imagedir[image])

Here the print commands the data that is required, e.g. it prins the ID's of the images.

In my script to read the data from the db:

import sqlite3

conn = sqlite3.connect('Materials.db')
c = conn.cursor()

c.execute('SELECT ID FROM images')
objectId = c.fetchall()
print(objectId)

I have a limited knowledge of sqlite3, but I would expect the print command in the second script to print the ID's found in the images table, from the Materials.db, yet it returns an empty array.

your for loops could also be improved btw. do for item in imagedir: and in the loop use the new variable item instead of imagedir[image] — Ma0
– Ma0, Commented Mar 6, 2017 at 13:09

Martijn Pieters · Accepted Answer · 2017-03-06 13:09:54Z

4

You need to commit your transaction when inserting:

for image in range(0,len(imagedir)):
    c.execute('INSERT INTO images(ID, url) VALUES(?,?)',(imagedir[image],'www.google.com'))
    print(imagedir[image])
conn.commit()

or use the connection as a context manager to auto-commit when the context exits:

with conn:
    for image in range(0,len(imagedir)):
        c.execute('INSERT INTO images(ID, url) VALUES(?,?)',(imagedir[image],'www.google.com'))
        print(imagedir[image])

This also ensures that the transaction is explicitly rolled back if there was an exception.

edited Mar 6, 2017 at 13:09

answered Mar 6, 2017 at 13:05

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mitchell van Zuylen Over a year ago

This works, thank you. Could you elaborate on using connection as a context manager?

Martijn Pieters Over a year ago

@MitchellvanZuylen: I linked to the relevant documentation section.

Martijn Pieters Over a year ago

@MitchellvanZuylen: for details on what context managers do, see the reference documentation.

Collectives™ on Stack Overflow

sqlite3 cursor.fetchall() returns an empty array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related