【图像识别】python、sqlite操作

复制代码
'''
sqlite3.exe

.open imagestags.db

# id,filename,tag1,tag1_score,tag2,tag2_score,tag3,tag3_score,tag4,tag4_score,tag5,tag5_score
create table imagestags(id integer, filename text, tag1 text, tag1_score REAL, tag2 text, tag2_score REAL, tag3 text, tag3_score REAL, tag4 text, tag4_score REAL, tag5 text, tag5_score REAL);


.databases

.tables

.separator ','

.import 'E:\2000_AI\0000_ImageCluster\1048576_results.csv' imagestags

select * from imagestags;

select * from sqlite_master where type="table";

select * from sqlite_master where type="table" and name="imagestags";

.schema imagestags 

 ALTER TABLE imagestags ADD COLUMN fullFeature Text; 

select tag1 || ',' || tag2  || ',' || tag3  || ',' || tag4  || ',' || tag5  from imagestags where id<5;

update imagestags 
set fullFeature = tag1 || ',' || tag2  || ',' || tag3  || ',' || tag4  || ',' || tag5;

select fullFeature  from imagestags where id<5;

select fullFeature  from imagestags where fullFeature like '%雪%';

select fullFeature  from imagestags where fullFeature like '%丛林%' and  fullFeature like '%雪景%';

select id as id,filename as filename,tag1 as tag1,tag1_score as tag1_score,tag2 as tag2,tag2_score as tag2_score,tag3 as tag3,tag3_score as tag3_score,tag4 as tag4,tag4_score as tag4_score,tag5 as tag5,tag5_score as tag5_score from imagestags  where  fullFeature like '%丛林%' and fullFeature like '%天空%' and fullFeature like '%雪景%';

select id as id,filename as filename,tag1 as tag1,tag1_score as tag1_score,tag2 as tag2,tag2_score as tag2_score,tag3 as tag3,tag3_score as tag3_score,tag4 as tag4,tag4_score as tag4_score,tag5 as tag5,tag5_score as tag5_score from imagestags  where  fullFeature like '%雪景%' and fullFeature like '%天空%' and fullFeature like '%丛林%' and fullFeature like '%山%';
select id as id,filename as filename,tag1 as tag1,tag1_score as tag1_score,tag2 as tag2,tag2_score as tag2_score,tag3 as tag3,tag3_score as tag3_score,tag4 as tag4,tag4_score as tag4_score,tag5 as tag5,tag5_score as tag5_score from imagestags  where  fullFeature like '%雪景%' and fullFeature like '%天空%' and fullFeature like '%丛林%' and fullFeature like '%山%' and fullFeature like '%日出日落%';

select ta.* ,tb.NO
from 
imagestags as ta,
(select tag1,tag2,tag3 row_number() over(partition by null order by null) as NO from imagestags group by tag1,tag2,tag3) as tb
where ta.tag1=tb.tag1 and ta.tag2=tb.tag2 and ta.tag3=tb.tag3

select ta.* ,tb.NO
from 
imagestags as ta,
(select tag1,tag2,tag3 row_number() over(partition by null order by null) as NO from imagestags group by tag1,tag2,tag3) as tb
where ta.tag1=tb.tag1 and ta.tag2=tb.tag2 and ta.tag3=tb.tag3;

select * from(
select * from
imagestags as ta
where ta.tag1 in (select tag1 from imagestags group by tag1 having count(*) >=2)  
and ta.tag2 in (select tag2 from imagestags group by tag2 having count(*) >=2)  
and ta.tag3 in (select tag3 from imagestags group by tag3 having count(*) >=2) ) order by tag1,tag2,tag3;


select * from(
select * from
imagestags as ta
where ta.tag1 in (select tag1 from imagestags group by tag1 having count(*) >=2)  
and ta.tag2 in (select tag2 from imagestags group by tag2 having count(*) >=2)  
and ta.tag3 in (select tag3 from imagestags group by tag3 having count(*) >=2)
 and ta.tag4 in (select tag4 from imagestags group by tag4 having count(*) >=2)
) order by tag1,tag2,tag3,tag4,tag5;

select * from(
select * from
imagestags as ta
where ta.tag1 in (select tag1 from imagestags group by tag1 having count(*) >=2)  
and ta.tag2 in (select tag2 from imagestags group by tag2 having count(*) >=2)  
and ta.tag3 in (select tag3 from imagestags group by tag3 having count(*) >=2)
 and ta.tag4 in (select tag4 from imagestags group by tag4 having count(*) >=2)
 and ta.tag5 in (select tag5 from imagestags group by tag5 having count(*) >=2)) order by tag1,tag2,tag3,tag4,tag5;


'''
复制代码

 

posted @   咸鱼翻身  阅读(50)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· 单线程的Redis速度为什么快?
· 展开说说关于C#中ORM框架的用法!
· Pantheons:用 TypeScript 打造主流大模型对话的一站式集成库
点击右上角即可分享
微信分享提示