How to Insert Data into Sqlite Database from Python

sqlite3 module provides nice ways to deal with Sqlite database. We will cover a way to create a table and pump data into the table with a right way.

I’d recommend this nice tool called DB Browser for SQLite. This tool is very useful when you browse the data in the database and execute SQL scripts.

First, let’s create a sample table.

    def create_table_hoge(self, conn):
        cur = conn.cursor()
        cur.execute('''CREATE TABLE HOGE (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        col1 text,
        col2 VARCHAR(10)
        )''')

We will pump bunch of dummy data into the table using the following code.

conn = sqlite3.connect(self.db_name)
        cur = conn.cursor()
        for i in range(1000):
            cur.execute('''INSERT INTO HOGE (col1, col2)
            VALUES (?, ?);
            ''', (f"test{i}",f"test{i+1}"))
            conn.commit()
            print(cur.lastrowid)

Note that cursor.execute method takes SQL statement and the parameter values as tuple. You can see the values are being accepted with question marks and actual values are in the tuple. I haven’t tried it but it probably prevents SQL injection rather than using a straight SQL like INSERT INTO HOGE (col1, col2) VALUES (‘test1’, ‘test2’).

When I browse HOGE table with DB Browser for SQLite, it looks like the following.

Author: admin

A software engineer in greater Seattle area

Leave a Reply

Your email address will not be published. Required fields are marked *