How can I import a csv file without primary key to sqlite3

Question

I want to populate a fresh database with inital data stored in csv files. I tried to use odo to populate an existing table with the content of a csv file. My files do not have a primary key and the number of columns do not match as the database has additional columns defined.

How can I use odo to achieve this?

from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
    __tablename__ = 'testtable'

    uid = Column(Integer, primary_key=True)
    colA = Column(String(50))
    colB = Column(String(50))
    comment = Column(String(100))

engine = create_engine('sqlite:///testdb.db')
Base.metadata.create_all(engine)

My csvfile looks like this:

col A;col B
aa;bb
ax;bx

This does not work:

from odo import odo
odo('csvfile.csv', 
    'sqlite:///testdb.db::testtable', 
    has_header=True)

Error message:

expected 4 columns but found 3 - filling the rest with NULL
INSERT failed: datatype mismatch

Does not work how? Do you get errors? If so, include them in the question. — Ilja Everilä, Dec 09 '17 at 14:29
I added the relevant error messages. The same works if I have the correct number of columns in the database and the csv file with matching column headers. — Bjoern, Dec 10 '17 at 08:51
Not a solution, but just a note that odo does not employ SQLAlchemy for the actual CSV to SQL import in case of SQLite: https://github.com/blaze/odo/blob/master/odo/backends/sql_csv.py#L63. The error is the result of the [`.import csvfile.csv testtable`](https://sqlite.org/cli.html#csv_import) command failing. — Ilja Everilä, Dec 11 '17 at 10:22

brddawg · Answer 1 · 2017-12-12T22:05:33.040

Looking at your initial table - your csv only comes with two columns and a third (likely the primary key) is being generated. Check that the comment is not required (or use some default text as in the example below) then explicitly put each value in a database column or consider deleting that column during an initial population and recreate after initially populating.

Here is an example of explicitly placing each value in a respective column. This addresses the issue of sqlite3 not aligning the number of inputs provided with the columns available:

import csv
# just my flavor...    

with open('csvfile.csv', 'r') as csvfile:
    # Next three lines detect dialect
    dialect = csv.Sniffer().sniff(csvfile.read(1024))
    # csvfile.seek(1,0) skips the first line of the file containing headers
    csvfile.seek(1,0)
    reader = csv.reader(csvfile, dialect)

for line in reader:
    A = line[0]
    B = line[1]
    rec = Test(colA=A, colB=B,comment='default text')
    session.add(rec)
    session.commit()

To avoid the problem all together, don't use comments until the database is populated:

class Test(Base):
    __tablename__ = 'testtable'
    uid = Column(Integer, primary_key=True)
    colA = Column(String(50))
    colB = Column(String(50))

This should allow the use of your odo as a means to populate the database.

How can I import a csv file without primary key to sqlite3

1 Answers1