Appending and Retrieving Lists from SqlLite3 using Python

Question

In my example i need to store a list of ints and a list of strings int a database. I'm currently just converting the entire list of ints and list of strings into a single int. I was wondering if this was an ideal workflow or if anyone had some alternative recommendations on how i could handle this. My concern with storing it as a string is how would i late then retrieve that information properly as a pythonic list of ints and strings?

import sqlite3
import hashlib


database = 'test.db'


def create_table():
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    cursor.execute("CREATE TABLE IF NOT EXISTS assets(url BLOB UNIQUE, colors BLOB, tags BLOB)")

    connection.commit()
    cursor.close()
    connection.close()


def kill_table():
    connection = sqlite3.connect(database)
    cursor = connection.cursor()
    cursor.execute('''DROP TABLE IF EXISTS assets''')
    connection.commit()


def read_from_db():
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    cursor.execute('SELECT * FROM assets')
    data = cursor.fetchall()
    print(len(data))
    for row in data:
        print(row)

    cursor.close()
    connection.close()


def get_data_entry(url=''):
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    url = hashlib.md5(url).hexdigest()

    cursor.execute('SELECT * FROM assets WHERE url=?', (url,))
    data = cursor.fetchall()

    if len(data) == 1:
        return data[0]
    else:
        print 'Found multiple entry instances'
        return False


def append_data_entries(url, colors, tags):
    '''
    Args:
        url (str): name of image item
        colors (list): list of dominant image colors
        tags (list): list of tags
    '''
    if not url or not colors or not tags:
        return False

    url = hashlib.md5(url).hexdigest()
    colors = str(colors)
    tags = str(tags)

    # updates or inserts
    cursor.execute("REPLACE INTO assets(url, colors, tags) VALUES (?, ?, ?)",
          (url, colors, tags))

    return True



if __name__ == '__main__':
    'Example'
    kill_table()
    create_table()

    # add test data to database
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    for i in range(10):
        url = '{num:08d}'.format(num=i)
        append_data_entries(url, '[[0,0,0],[10,10,10],[50,50,50]]','["red","green","blue","orange"]')

    connection.commit()
    cursor.close()
    connection.close()


    read_from_db()
    print 'ITEM:', get_data_entry('00000006')

Answer 1

When retrieving the data, it returns a string tuple as expected, then you must convert each element if necessary in a suitable data type, for this particular case the ast.literal_eval function should work:

def convert(in_data):
    def cvt(data):
        try:
            return ast.literal_eval(data)
        except Exception:
            return str(data)
    return tuple(map(cvt, in_data))

Example code:

import sqlite3
import hashlib
import ast

database = 'test.db'


def create_table():
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    cursor.execute("CREATE TABLE IF NOT EXISTS assets(url BLOB UNIQUE, colors BLOB, tags BLOB)")

    connection.commit()
    cursor.close()
    connection.close()


def kill_table():
    connection = sqlite3.connect(database)
    cursor = connection.cursor()
    cursor.execute('''DROP TABLE IF EXISTS assets''')
    connection.commit()

def convert(in_data):
    def cvt(data):
        try:
            return ast.literal_eval(data)
        except Exception:
            return str(data)
    return tuple(map(cvt, in_data))

def read_from_db():
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    cursor.execute('SELECT * FROM assets')
    data = cursor.fetchall()
    print(len(data))
    for row in data:
        print(convert(row))

    cursor.close()
    connection.close()


def get_data_entry(url=''):
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    url = hashlib.md5(url).hexdigest()

    cursor.execute('SELECT * FROM assets WHERE url=?', (url,))
    data = cursor.fetchall()

    if len(data) == 1:
        return convert(data[0])
    else:
        print('Found multiple entry instances')
        return False


def append_data_entries(url, colors, tags):
    '''
    Args:
        url (str): name of image item
        colors (list): list of dominant image colors
        tags (list): list of tags
    '''
    if not url or not colors or not tags:
        return False

    url = hashlib.md5(url).hexdigest()
    colors = str(colors)
    tags = str(tags)

    # updates or inserts
    cursor.execute("REPLACE INTO assets(url, colors, tags) VALUES (?, ?, ?)",
          (url, colors, tags))

    return True



if __name__ == '__main__':
    'Example'
    kill_table()
    create_table()

    # add test data to database
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    for i in range(10):
        url = '{num:08d}'.format(num=i)
        append_data_entries(url, '[[0,0,0],[10,10,10],[50,50,50]]','["red","green","blue","orange"]')

    connection.commit()
    cursor.close()
    connection.close()


    read_from_db()
    print('ITEM:', get_data_entry('00000006'))