mjhjmtu

I have written a program that outputs the result to the standard output in strict pure CSV form (every line represents a single record and contain the same set of comma-separated fields, fields only contain lowercase English letters, numbers and dots, no spaces, no quotes and no symbols that might need to be escaped/encoded).

How do I redirect this output into an SQLite table that it fits into perfectly?

It would be great if I could control whether I want constraint-breaking (e.g. having the same primary/secondary key as the records already in the table) replace existing records or be discarded silently.

Of course I might build direct SQLite database output support in the program itself but I would prefer going the unix-way if possible.

Two approaches:

Sample test.csv file:

GroupName,Groupcode,GroupOwner,GroupCategoryID 
System Administrators,sysadmin,13456,100
Independence High Teachers,HS Teachers,,101
John Glenn Middle Teachers,MS Teachers,13458,102
Liberty Elementary Teachers,Elem Teachers,13559,103
1st Grade Teachers,1stgrade,,104
2nd Grade Teachers,2nsgrade,13561,105
3rd Grade Teachers,3rdgrade,13562,106
Guidance Department,guidance,,107

1) With csvkit (a suite of command-line tools for converting to and working with CSV)

Import into sqlite3 database:

csvsql --db sqlite:///test_db --tables test_tbl --insert test.csv

If no input csv file was specified it'll accept csv data from stdin:

... | csvsql --db sqlite:///test_db --tables test_tbl --insert

To extract data from sqlite database:

sql2csv --db sqlite:///test_db --query 'select * from test_tbl limit 3'

The output:

GroupName,Groupcode,GroupOwner,GroupCategoryID 
System Administrators,sysadmin,13456,100
Independence High Teachers,HS Teachers,,101
John Glenn Middle Teachers,MS Teachers,13458,102

2) With sqlite3 command-line tool (allows the user to manually enter and execute SQL statements against an SQLite database)

Use the ".import" command to import CSV (comma separated value) data
into an SQLite table. The ".import" command takes two arguments which
are the name of the disk file from which CSV data is to be read and
the name of the SQLite table into which the CSV data is to be
inserted.

Note that it is important to set the "mode" to "csv" before running
the ".import" command. This is necessary to prevent the command-line
shell from trying to interpret the input file text as some other
format.

$ sqlite3
sqlite> .mode csv
sqlite> .import test.csv test_tbl
sqlite> select GroupName,Groupcode from test_tbl limit 5;
"System Administrators",sysadmin
"Independence High Teachers","HS Teachers"
"John Glenn Middle Teachers","MS Teachers"
"Liberty Elementary Teachers","Elem Teachers"
"1st Grade Teachers",1stgrade
sqlite>