Saturday, April 3, 2021

Recent Questions - Stack Overflow

Recent Questions - Stack Overflow

Recent Questions - Stack Overflow


An efficient way to turn each distinct value into a column in SQLite

Posted: 03 Apr 2021 12:13 PM PDT

I can't seem to find a way to get all entries from a document as a single table in a database structured as such:

docs_table:

id | date  0  | 210301  1  | 210301  2  | 210302  3  | 210303  

doc_entries_table:

document_id | entry_name  | entry_value            0 | first_ent   | 290            0 | second_ent  | John            1 | first_ent   | 290            1 | second_ent  | Anne            1 | third_ent   | 1            2 | first_ent   | 291            2 | second_ent  | Frank            2 | third_ent   | 1            3 | first_ent   | 290            3 | second_ent  | Matthew  

Turn them into a column and fill them afterwards. Basically, have them merged into this expanded table:

result_table:

id | date   | first_ent | second_ent | third_ent  0  | 210301 | 290       | John       | NULL       1  | 210301 | 290       | Anne       | 1          2  | 210302 | 291       | Frank      | 1          3  | 210303 | 290       | Matthew    | NULL    

The catch is that there may be thousands of documents with around ten entries each. How can this be implemented in a relatively efficient way?

When does that "r", for raw string, not make a difference

Posted: 03 Apr 2021 12:13 PM PDT

I wrote some simple test code to experiment with.

#begin code Python 2.7.12 running in Windows command window  import re  s2='''corn grows  higher\n   still.  '''    print (s2)  print (re.sub('\n', '~', s2),"test a")  print (re.sub(r'\n', '~', s2),"test ar")  print (re.sub('\s', '~', s2),"test b")  print (re.sub(r'\s', '~', s2),"test br")  

##begin output to screen:#################### corn grows higher

still.

('corn grows~higher~~ still.~', 'test a') ('corn grows~higher~~ still.~', 'test ar') ('corn~grows~higher~~~still.~', 'test b') ('corn~grows~higher~~~still.~', 'test br') ##

No comments:

Post a Comment