Is there a SQLite equivalent to COPY from PostgreSQL?

Question

I have local tab delimited raw data files "...\publisher.txt" and "...\field.txt" that I would like to load into a local SQLite database. The corresponding tables are already defined in the local database. I am accessing the database through the python-sql library in an ipython notebook. Is there a simple way to load these text files into the database?

CLI command 'readfile' doesn't seem to work in python context:

INSERT INTO Pub(k,p) VALUES('pubFile.txt',readfile('pubFile.txt'));

Throws error:

(sqlite3.OperationalError) no such function: readfile
[SQL: INSERT INTO Pub(k,p) VALUES('pubFile.txt',readfile('pubFile.txt'));]
(Background on this error at: http://sqlalche.me/e/e3q8)

Does this answer your question? [Import CSV to SQLite](https://stackoverflow.com/questions/14947916/import-csv-to-sqlite) — Klaus D., Dec 05 '20 at 06:24
Without going in codes, in my case, I import the file in `excel` in a table format, then make a copy from `excel` to the opened table in `SQLite studio` after inserting empty rows before past. — Nour-Allah Hussein, Dec 05 '20 at 06:26

score 0 · Accepted Answer · answered Dec 05 '20 at 06:26

You may read the input file into a string and then insert it:

sql = "INSERT INTO Pub (k, p) VALUES ('pubFile.txt', ?)"
with open ("pubFile.txt", "r") as myfile:
    data = '\n'.join(myfile.readlines())
cur = conn.cursor()
cur.execute(sql, (data,))
conn.commit()

score 0 · Answer 2 · answered Nov 17 '21 at 14:56

No, there isn't such a command in SQLite (any longer). That feature was removed, and has been replaced by the SQLite CLI's .import statement.

See the official documentation:

The COPY command is available in SQLite version 2.8 and earlier. The COPY command has been removed from SQLite version 3.0 due to complications in trying to support it in a mixed UTF-8/16 environment. In version 3.0, the command-line shell contains a new command .import that can be used as a substitute for COPY.

The COPY command is an extension used to load large amounts of data into a table. It is modeled after a similar command found in PostgreSQL. In fact, the SQLite COPY command is specifically designed to be able to read the output of the PostgreSQL dump utility pg_dump so that data can be easily transferred from PostgreSQL into SQLite.

A sample code to load a text file into an SQLite database via the CLI is as below:

sqlite3 test.db ".import "test.txt" test_table_name"

Is there a SQLite equivalent to COPY from PostgreSQL?

2 Answers2