Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQL question
#1
Hi,
I have data in an FTS5 sql table, but you can sql update it like any other table. (but no field types)
Say the table is called "abc", and has 3 fields (filename, freeText,register)
The table has 42.000 records;
The problem is in the filename, consider this:
VST-098.png should be VST-0098.png
VST-125.png should be VST-0125.png
VST-995.png should be VST-0995.png
VST-1289.png is ok, with 4 numeric digits.

So I have to test on the numeric part, if it's 3 chars, add leading zero.
Normally in python, a piece of cake, but inside an sql table, how to ?
If it's too complicated, I can also rename the 42.000 image files, maybe that is the wiser option?
thx,
Paul

Edit: the KISS solution is modify the filenames. 5 minute job.
It is more important to do the right thing, than to do the thing right.(P.Drucker)
Better is the enemy of good. (Montesquieu) = French version for 'kiss'.
Reply
#2
(Jun-02-2023, 08:43 AM)DPaul Wrote: ...
So I have to test on the numeric part, if it's 3 chars, add leading zero.
Normally in python, a piece of cake, but inside an sql table, how to ?

Maybe I'm missing something, but surly it's just a case of writing a Python script to read said filename from the DB, check the criteria, change the filename if needs be and then, if changed, write it back to the DB, all in a for loop, no?

To add: sorry, I've misread the question:

(Jun-02-2023, 08:43 AM)DPaul Wrote: I have data in an FTS5 sql table...

I've never used the FTS5 virtual table module, so maybe that's the issue?

Maybe this link will help
Sig:
>>> import this

The UNIX philosophy: "Do one thing, and do it well."

"The danger of computers becoming like humans is not as great as the danger of humans becoming like computers." :~ Konrad Zuse

"Everything should be made as simple as possible, but not simpler." :~ Albert Einstein
Reply
#3
(Jun-03-2023, 05:05 AM)rob101 Wrote: I've never used the FTS5 virtual table module, so maybe that's the issue?
FTS5 behaves most of the time like other tables.
The problem was that I did not know how to modify 1 field in
40.000 records, using specific logic, making sure the right logic
was applied in every case.
But, I followed the KISS solution, highly recommended !
Paul
It is more important to do the right thing, than to do the thing right.(P.Drucker)
Better is the enemy of good. (Montesquieu) = French version for 'kiss'.
Reply
#4
(Jun-03-2023, 06:22 AM)DPaul Wrote: I followed the KISS solution, highly recommended !

Indeed. That's the essence of the last line of my sig 🙂 :
"Everything should be made as simple as possible, but not simpler." :~ Albert Einstein

Can I ask: given that this 'FTS5' thing seems to be causing you some hassle [as in the posts here], why are you using it? Why not use a regular SQL Table?
Sig:
>>> import this

The UNIX philosophy: "Do one thing, and do it well."

"The danger of computers becoming like humans is not as great as the danger of humans becoming like computers." :~ Konrad Zuse

"Everything should be made as simple as possible, but not simpler." :~ Albert Einstein
Reply
#5
(Jun-03-2023, 06:57 AM)rob101 Wrote: Can I ask: given that this 'FTS5' thing seems to be causing you some hassle [as in the posts here], why are you using it? Why not use a regular SQL Table?
FTS stand For "Full Text Search". That is exactly what I am doing with zillions of prayer cards.
The problems with FTS5 are caused by the lack of good examples and good documentation.
Especially what tokenization is concerned.
I'm using it with the trigram tokenizer, and that seems to work wonderfully well(for what I need).
I must admit, without understanding it 100%. As usual, KISS is the clue!
Maybe it would even be better if somebody would write a "tokenizer for dummies". Wink
Paul
It is more important to do the right thing, than to do the thing right.(P.Drucker)
Better is the enemy of good. (Montesquieu) = French version for 'kiss'.
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020