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 !
