0

EDIT: I have a python app that is creating a table in the Database. It creates a random number of rows. I do not the know the exact the number of rows that are created in the table. Each rows correspond to the metadata of my pictures, in the example below, only 6 rows are created:

enter image description here

I want to use FTS4 full text search capability to parse the columns for a key words.

So Far, I understand that I need to create a virtual table and import every thing from the original table to the virtual one. Using this SO thread I understand that I need do : INSERT INTO bar SELECT * FROM other_table .

So, what is the syntax to create the Virtual one and import the content from the CONTENTSEARCHINDEX table to the CONTENTSEARCHINDEXvirt virtual table : 

conn.execute('''CREATE VIRTUAL TABLE IF NOT EXISTS CONTENTSEARCHINDEXvirt USING FTS4;''')
conn.execute("INSERT INTO CONTENTSEARCHINDEXvirt SELECT * FROM CONTENTSEARCHINDEX")

I got this error : 

sqlite3.OperationalError: table CONTENTSEARCHINDEXvirt has 1 columns but 7 values were supplied

So as CL said, this syntax tries to import this random number of columns to the default column is created .

Then What is the best Way to do that ?

I can get the list my -unknown- columns by using : 

# Listing the number of columns from initial DB as a list.
connection = sqlite3.connect(DBtoReadAndParse)
cursor = connection.execute('select * from CONTENTSEARCHINDEX;')
namescolumns = list(map(lambda x: x[0], cursor.description))
MouIdri
  • 1,300
  • 1
  • 18
  • 37
  • Do you really want to index all 21 columns? And how should the database know how many columns you want? – CL. May 31 '17 at 16:29
  • @ CL: This is the purpose of my question. I am a newbee on SQLite ( and DB ). so I have to specify the number of columns to create in my virtual table ? If yes, What would be the syntax ? -since I do not know the number of columns my script will create, because, I do not know initially all the characteristics of my students. – MouIdri Jun 01 '17 at 07:41
  • Don't you know what columns your table has? – CL. Jun 01 '17 at 08:54
  • No. the App creates Db dynamically and deletes it afterwards. From One Api Call to the other, this App creates a different number of columns every time is is called. – MouIdri Jun 01 '17 at 08:58
  • Are you asking how to find out which columns a table has, or how to create an FTS table? – CL. Jun 01 '17 at 08:59
  • I know how to get this : cursor = connection.execute('select * from CONTENTSEARCHINDEX;') and then namescolumns = list(map(lambda x: x[0], cursor.description)) . But I cannot use "alter" to recursively add the columns in my virtual table. if this what you mean. – MouIdri Jun 01 '17 at 09:20

1 Answers1

0

The SQLite documentation documents how to create FTS tables: CREATE VIRTUAL TABLE, Creating and Destroying FTS Tables.

The virtual table should contain those columns in which you want to do a full-text search. To create it, construct a string of the form

CREATE VIRTAL TABLE name USING FTS4(key, metadata_0, and_so_on)
CL.
  • 173,858
  • 17
  • 217
  • 259
  • I went there before sending my question to SO. not sure my issue is on creation, but on filling my virtual DB. So I have to find a way to get the content of my columns and the number of columns in my initial DB then recursively fill my virtual DB depending on this number of Columns. ? – MouIdri Jun 01 '17 at 08:49