import csv into sqlite3 insert failed

Question

I am trying to import nodes.csv file into sqlite3 table - link to file

this is my code-

CREATE TABLE nodes (
    id INTEGER PRIMARY KEY NOT NULL,
    lat REAL,
    lon REAL,
    user TEXT,
    uid INTEGER,
    version INTEGER,
    changeset INTEGER,
    timestamp TEXT
);

.import nodes.csv nodes

I managed to import this file without the primary key designation, but once I add primary key to the id field it gives me the following error

nodes.csv:xxx: INSERT failed: datatype mismatch

for every line in the file where xxx is the line number. I am very new to sqlite3 and will appreciate your insights on the matter.

score 2 · Answer 1 · answered Jun 09 '18 at 08:37

CSV files contain strings. You cannot insert a string into an integer column. (Usually, SQLite uses dynamic typing, but the INTEGER PRIMARY KEY is special.)

Import into a temporary table, then copy the data to the real table, while converting the numbers into actual numbers:

CREATE TABLE nodes_temp(...);
.import nodes.csv nodes_temp

CREATE TABLE nodes(...);
INSERT INTO nodes
SELECT CAST(id AS INTEGER), CAST(lat AS REAL), ...
FROM nodes_temp;

score 1 · Answer 2 · answered Jun 11 '18 at 07:14

Since i am working from python pandas provides an easier way - credit to Tennessee Leeuwenburg

c_nodes = "CREATE TABLE nodes(id INTEGER PRIMARY KEY NOT NULL, lat REAL, lon REAL, user TEXT, uid INTEGER, \
version INTEGER, changeset INTEGER, timestamp TEXT);"

conn = sqlite3.connect('osm.db')
cursor = conn.cursor()

cursor.execute(c_nodes)
df_nodes = pd.read_csv('nodes.csv')
df_nodes.to_sql('nodes',conn, if_exists='append', index=False)

import csv into sqlite3 insert failed

2 Answers2