SQLite: query with duplicate column names |> DataFrame

Specific Domains Data
,
2

with some metaprogramming tricks - it is not so hard - on the SQLite part

my proposal:

You can query the table columns - with
select name from pragma_table_info('texample1')
so you can replace the * with the column lists

So the expanded variable list (*) can be created :

  • in Julia
  • or with a simple SQL

example - variable lists with 2 tables in sql:

select group_concat(name,',')
from
(
-- columns from table1
select ' texample1.'||name as name from pragma_table_info('texample1')
union all
-- columns from table2 AND not in table1
select ' texample2.'||name as name from pragma_table_info('texample2') where name not in ( select name from pragma_table_info('texample1') )
union all
-- columns from table2 AND in table1 --> renaming 
select ' texample2.'||name||' as prefix_'||name as name from pragma_table_info('texample2') where name in ( select name from pragma_table_info('texample1') )
)
;

with these tables:

CREATE TABLE texample1 (
   t_id INTEGER ,
   name TEXT,
   x1   INTEGER,
   x2   INTEGER
);

CREATE TABLE texample2 (
   t_id INTEGER ,
   category TEXT,
   x1   INTEGER,   
   m1   INTEGER,
   m2   INTEGER
);

the select_variables results - for replacing *
texample1.t_id, texample1.name, texample1.x1, texample1.x2, texample2.category, texample2.m1, texample2.m2, texample2.t_id as prefix_t_id, texample2.x1 as prefix_x1

And your final query will be

select
  -- generated variable list start
      texample1.t_id
    , texample1.name
    , texample1.x1
    , texample1.x2
    , texample2.category
    , texample2.m1
    , texample2.m2
    , texample2.t_id as prefix_t_id
    , texample2.x1   as prefix_x1
  -- generated variable list end
from texample1 join texample2 on texample2.t_id = texample2.t_id
;

IMHO:Sometimes it is not safe to rely on DataFrame for renaming
The next version of SQLite may return the sql result in a different column order, and your results will be different.

so rename in the SQL side !