Inserting a numpy array into a sqlite table

Question

I have a fairly large array that I am trying to insert into an SQL table. I would like to do this without using loops.

For an array:

arr = np.array([['a',1,2],['b',3,4], ...])

I am trying to insert it:

db.execute("""INSERT INTO table(column1, column2, column3)
VALUES (%s)"""% (','.join('?'*len(arr))), arr)

However, this raises the error that I am trying to insert 100 values for 3 columns.

What would be the correct way of writing this?

Does [this](https://stackoverflow.com/questions/18621513/python-insert-numpy-array-into-sqlite3-database) answer your question? — snakecharmerb, Aug 26 '23 at 06:27

score 1 · Answer 1 · answered Aug 25 '23 at 21:21

Here is an example how you can insert data from the numpy array to a sqlite database.

First create example memory db:

import sqlite3

import numpy as np

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

create_table_query = """
    CREATE TABLE data (
        column1 TEXT,
        column2 INTEGER,
        column3 INTEGER
    )
"""
cursor.execute(create_table_query)
conn.commit()

To insert the values:

arr = np.array([["a", 1, 2], ["b", 3, 4]])
insert_query = "INSERT INTO data (column1, column2, column3) VALUES (?, ?, ?)"
cursor.executemany(insert_query, arr)
conn.commit()

To check it, we do SELECT and close the db:

select_query = "SELECT * FROM data"
cursor.execute(select_query)
all_data = cursor.fetchall()

for col1, col2, col3 in all_data:
    print(f"{col1:<10} {col2:<10} {col3:<10}")

conn.close()

Prints:

a          1          2         
b          3          4

Inserting a numpy array into a sqlite table

1 Answers1