CODEWITHSUNDEEP
pythonsqlite
Mitchell van Zuylen
Mitchell van Zuylen

Reputation: 4125

sqlite3 cursor.fetchall() returns an empty array

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.

Upvotes: 1

Views: 3058

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121594

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.

Upvotes: 4

Related Questions