0

"i am developing a mobile application with Ionic for a book that has a lot of content, grouping in
several parts, subpart, title and chapter. I chose to use SQLite, and I'm looking for a way to load all its contents into my SQLite database and if anyone has an idea or suggestion to help me do things well I'll be delighted."

MDN993
  • 11
  • 4
  • 1
    Does this answer your question? [Load contents in text files to sqlite table?](https://stackoverflow.com/questions/15317929/load-contents-in-text-files-to-sqlite-table) – peak Oct 30 '19 at 23:02

2 Answers2

1

The following illustrates how to use the python sqlite3 module to import a (UTF-8) text file:

import sqlite3

def create_table():
    conn.execute("""CREATE TABLE IF NOT EXISTS mytext( mytext TEXT );""")

def insert_file(filename):
    sql = "INSERT INTO mytext(mytext) VALUES(?)"

    cursor.execute(sql, (open(filename, "r").read(),))
    conn.commit()

db_file_name = 'text-file.db'
conn = sqlite3.connect(db_file_name)
cursor = conn.cursor()
create_table()

insert_file("text.txt")

conn.close()

(Tested with python3.)

peak
  • 105,803
  • 17
  • 152
  • 177
0

There are several ways to import a text file, e.g. as a BLOB, on a line-to-row basis, using sqlite's "archive" mode, and so on.

If you want the entire file as a single TEXT cell in a table, then (subject to the limitation described below) you can use .import by carefully selecting values for the separators.

Multibyte separators are not supported when importing, but control characters such as Control-A and Control-B can be used.

So you could proceed like so:

CREATE TABLE text("text" text)';
.separator ^A ^B 
.import text.txt text

where ^A should be replaced by a literal control-A, and similarly for ^B.

Limitation

The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000).

peak
  • 105,803
  • 17
  • 152
  • 177
  • Your example would be more clear if you renamed your table, column, and file. The word "text" shows up in too many places to make it clear. – Karl Kowallis Dec 23 '21 at 14:34