Sunday, April 30, 2017

*** sqlite 3 rls.db , result table

sqlite3 on rls.db

see https://www.tutorialspoint.com/sqlite/


.open rls.db
.databases
.tables
.separator ::
.headers on
.mode column
select distinct experiment from result_experiment limit 20;
.indices
.indices set
.width 5
select * from result  limit 1;

/* The following select can take rls and its reference rls */
select experiments,set_name,set_strain,set_background,set_genotype,
 set_lifespan_mean,ref_genotype,ref_lifespan_mean
 from result  limit 2;

CREATE TEMPORARY TABLE my_RLS_out AS
SELECT experiments,set_name,set_strain,set_background,set_genotype,
 set_lifespan_mean,ref_genotype,ref_lifespan_mean
 FROM result; 

DROP TABLE my_RLS_out;
CREATE TEMPORARY TABLE my_RLS_out AS
SELECT experiments,set_name,set_strain,set_background,set_genotype,
 set_lifespan_mean,ref_genotype,ref_lifespan_mean, ranksum_p
 FROM result
 WHERE ranksum_p < 0.05
 ;

select * from my_RLS_out limit 5;    

select distinct set_genotype from my_RLS_out; 








sqlite> .indices result
result_percent_change
result_pooled_by
result_ranksum_p
result_ref_background
result_ref_genotype
result_ref_locus_tag
result_ref_mating_type
result_ref_media
result_ref_name
result_ref_strain
result_ref_temperature
result_set_background
result_set_genotype
result_set_lifespan_mean
result_set_locus_tag
result_set_mating_type
result_set_media
result_set_name
result_set_strain

result_set_temperature

#output result table into csv
sqlite> .headers on
sqlite> .mode csv
sqlite> .output qintest.csv
sqlite> select * from result;
sqlite> .quit
emc313b02:data hqin$ ll
total 146712
-rw-r--r--  1 hqin  staff   8.5M Apr 30 23:31 qintest.csv
lrwxr-xr-x  1 hqin  staff    15B Apr 30 20:07 rls.db -> rls_20160802.db
-rw-------  1 hqin  staff   8.3M Aug  9  2016 rls_2016-08-02.csv
-rw-------  1 hqin  staff    55M Aug  9  2016 rls_20160802.db
emc313b02:data hqin$ less qintest.csv 
emc313b02:data hqin$ open qintest.csv 

emc313b02:data hqin$ 




Note
Based on
http://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql
Sqlite database is not easily convertable to mysql database. 

No comments:

Post a Comment