CODEWITHSUNDEEP
pythonsqlite
pr0grammingIsFun
pr0grammingIsFun

Reputation: 35

How do you insert a list in a sqlite3 database

I only know how to enter strings and numbers, but is there a way to enter a list? Basically, I have a dictionary and each key is being put into the database. The value to each key is a list and I want this in the database as well.

Upvotes: 1

Views: 619

Answers (2)

Catrell Washington
Catrell Washington

Reputation: 83

With sqlite3 I was able to execute this script that drops a database and rewrites it with new data using a list named data. This may or may not answer your specific question:

from views import db
from config import DATABASE_PATH

import sqlite3
from datetime import datetime

with sqlite3.connect(DATABASE_PATH) as connection:
    c = connection.cursor()

    c.execute("""ALTER TABLE tasks RENAME TO old_tasks""")

    db.create_all()

    c.execute("""SELECT name, due_date, priority,
                status FROM old_tasks ORDER BY task_id ASC""")

    data = [(row[0], row[1], row[2], row[3],
        datetime.now(), 1) for row in c.fetchall()]

 c.executemany("""INSERT INTO tasks (name, due_date, priority, status,
                    posted_date, user_id) VALUES (?, ?, ?, ?, ?, ?)""", data)


    c.execute("DROP TABLE old_tasks")

When the above script is executed, data takes in 4 sub lists and uses the data from them to create a new database.

Hope I helped!

Upvotes: 0

falsetru
falsetru

Reputation: 369454

SQLite3 does not have array data type. (See Datatypes in SQLite version 3)

But, you can serialize to insert it to sqlite3 table.

For example, you can convert the list into json string, and use the string to insert into the table. (json.dumps)

cursor.execute('INSERT INTO a_table (..., a_list_field) values (..., ?)',
               [..., json.dumps(a_list_object)])

When you select it, convert it back to list by loading json string. (json.loads)

Upvotes: 1

Related Questions