How to insert data from JSON file into SQLite Database from Batch file-CodePudding

i would like to insert data from a JSON-File into a SQLite-Databse with an existing table from a batch file.

First i filter the flawed JSON-File with jq afterwards i open the database and try to insert the data, but after opening the database the batch file stops at receiving commands from sqlite3.

This how the batch file looks like:

jq -r ".[]" D:\path\2\json_file\somedata.json > D:\path\2\json_file\somedata_new.json

sqlite3 "C:\path\2\DB_Folder\db1.sqlite" 

INSERT INTO mytable (col1, col2, col3, col4) SELECT json_extract(value, '$.col1'), json_extract(value, '$.col2'), json_extract(value, '$.col3'), json_extract(value, '$.col4') FROM json_each(readfile('D:\path\2\json_file\somedata_new.json'));

But after executing sqlite3 "C:\path\2\DB_Folder\db1.sqlite" the batch file stops at receiving commands for sqlite3 and the INSERT-Statement is ignored.

If i execute the commands directly in the command line everything works fine.

I tried to create a file like here with the INSERT-Statement but mytable remains empty.

Can anybody help me?

Btw it is necessary to input the data automatically without any users actions.

Thanks for help in advance.

CodePudding user response：

From the SQLite documentation:

One way to use sqlite3 in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This works fine and is appropriate in many circumstances. But as an added convenience, sqlite3 allows a single SQL command to be entered on the command line as a second argument after the database name. When the sqlite3 program is launched with two arguments, the second argument is passed to the SQLite library for processing, the query results are printed on standard output in list mode, and the program exits.

So you can do :

echo "insert into ...." | sqlite3 "C:\path\2\DB_Folder\db1.sqlite"

sqlite3 "C:\path\2\DB_Folder\db1.sqlite" "insert into ...."

In both cases, mind the escaping/quoting rules for cmd.exe.

As a final alternative, you can use jq to convert the JSON into a CSV file and use the far simpler SQLite .import command.

jq -r ".[] | [ .col1, .col2, .col3, .col4 ] | @csv" somedata.json > somedata.csv
sqlite3 "C:\path\2\DB_Folder\db1.sqlite" ".import somedata.csv mytable"