I’m writing my first script with sqlite and have not been able to insert a fictional observation into the data with insert into. Here is my attempt:

from pandas import read_csv, read_sql_query
from pathlib import Path
from sqlite3 import connect

Path('database.db').touch()
conn = connect('database.db')
c = conn.cursor()
c.execute(
    'CREATE TABLE IF NOT EXISTS spiders ('
        'record_id INT, month INT, day INT, year INT,'
        'plot_id INT, species_id CHAR(2), sex CHAR(1), hindfoot_length FLOAT,'
        ' weight FLOAT'
        ')'
        )
data = read_csv('surveys.csv')
data = data.dropna()
data.to_sql('spiders', conn, if_exists='append', index=False)

query_1 = read_sql_query('SELECT * FROM spiders', conn)
print('The first five rows of the data:\n')
print(query_1.head())

c.execute(
    'INSERT INTO spiders ('
        'record_id, month, day, year, plot_id, species_id, sex, '
        'hindfoot_length, weight'
        ') VALUES (0, 7, 31, 2023, 2, "DM", "F", 99.0, 60.0)', conn
    )

Here is the output with traceback:

The first five rows of the data:

   record_id  month  day  year  plot_id species_id sex  hindfoot_length  weight
0         63      8   19  1977        3         DM   M             35.0    40.0
1         64      8   19  1977        7         DM   M             37.0    48.0
2         65      8   19  1977        4         DM   F             34.0    29.0
3         66      8   19  1977        4         DM   F             35.0    46.0
4         67      8   19  1977        7         DM   M             35.0    36.0

Traceback (most recent call last):

  File ~/mambaforge/envs/spyder-env/lib/python3.11/site-packages/spyder_kernels/py3compat.py:356 in compat_exec
    exec(code, globals, locals)

  File ~/Desktop/data/sql_demo.py:38
    c.execute(

ProgrammingError: parameters are of unsupported type

What is wrong in my script?

>Solution :

The problem is the conn parameter. You do not need to pass the connection to execute. It gets the connection from the cursor. That parameter is supposed to contain the list of items to be substituted into the query, and execute did not recognize the type of that parameter.

If you’re not doing substitution, do:

c.execute(
    'INSERT INTO spiders ('
        'record_id, month, day, year, plot_id, species_id, sex, '
        'hindfoot_length, weight'
        ') VALUES (0, 7, 31, 2023, 2, "DM", "F", 99.0, 60.0)'
    )

but it would be better practice to do:

c.execute(
    'INSERT INTO spiders ('
        'record_id, month, day, year, plot_id, species_id, sex, '
        'hindfoot_length, weight'
        ') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);',
        (0, 7, 31, 2023, 2, "DM", "F", 99.0, 60.0)
    )