what is the matter with the select_string wrapped in py_sqlite3?

Question

I can select a record in sqlitemanager (firefox plugin).Please see the attatchment of the selected record.The database is here , http://pan.baidu.com/s/1eQ04tlo . You can download it and test the sql statement.

select  capital.代码 as code,quote.close as close, quote.close*capital.股份总数/10000 as market_cap,     
    balance.负债合计/balance.资产总计 as debt_ratio,                                                        
    profit.归属于母公司所有者的净利润/balance.'所有者权益(或股东权益)合计' as roe,            
    profit.归属于母公司所有者的净利润/balance.'负债和所有者权益(或股东权益)总计' as roa ,                  
    quote.close*capital.股份总数/profit.归属于母公司所有者的净利润 as pe,                                   
    quote.close*capital.股份总数/balance.资产总计  as pb                                                     
    from profile,capital,quote,balance,profit                                                               
    where capital.代码=300001
    and profile.代码=capital.代码  
    and profile.代码=quote.code   
    and profile.代码=balance.代码
    and profile.代码=profit.代码  
    and quote.date="20140523"     
    and balance.报告日期="20131231"      
    and profit.报告日期="20131231";

When i wrap it in python code ,i can't get the selected record,What is the matter?

# -*- coding: utf-8 -*-
import os,re,sqlite3
db_name='china.sqlite'
target_dir='e:\\workspace\\data\\'
con = sqlite3.connect(target_dir+db_name)
cur=con.cursor()
select_str='''select  capital.代码 as code,quote.close as close, quote.close*capital.股份总数/10000 as market_cap,     
    balance.负债合计/balance.资产总计 as debt_ratio,                                                        
    profit.归属于母公司所有者的净利润/balance.'所有者权益(或股东权益)合计' as roe,            
    profit.归属于母公司所有者的净利润/balance.'负债和所有者权益(或股东权益)总计' as roa ,                  
    quote.close*capital.股份总数/profit.归属于母公司所有者的净利润 as pe,                                   
    quote.close*capital.股份总数/balance.资产总计  as pb                                                     
    from profile,capital,quote,balance,profit                                                               
    where  capital.代码=300001   
    and profile.代码=capital.代码  
    and profile.代码=quote.code   
    and profile.代码=balance.代码
    and profile.代码=profit.代码  
    and quote.date='20140523'     
    and balance.报告日期='20131231'      
    and profit.报告日期='20131231';'''
cur.execute(select_str)
print(cur.fetchall())

it's so strange for me to understand that that code1 can run,code2 can not run.

code1

import os,re,sqlite3
db_name='china.sqlite'
target_dir='e:\\workspace\\data\\'
con = sqlite3.connect(target_dir+db_name)
cur=con.cursor()
select_str='''select  capital.代码 as code,quote.close as close, quote.close*capital.股份总数/10000 as market_cap,     
    balance.负债合计/balance.资产总计 as debt_ratio,                                                        
    profit.归属于母公司所有者的净利润/balance.'所有者权益(或股东权益)合计' as roe,            
    profit.归属于母公司所有者的净利润/balance.'负债和所有者权益(或股东权益)总计' as roa ,                  
    quote.close*capital.股份总数/profit.归属于母公司所有者的净利润 as pe,                                   
    quote.close*capital.股份总数/balance.资产总计  as pb                                                     
    from profile,capital,quote,balance,profit                                                               
    where  capital.代码='300001'  
    and profile.代码='300001'  
    and quote.code='300001'  
    and balance.代码='300001'
    and profit.代码='300001'
    and quote.date='20140523'     
    and balance.报告日期='20131231'      
    and profit.报告日期='20131231';'''
cur.execute(select_str)
print(cur.fetchall())

code2

import os,re,sqlite3
db_name='china.sqlite'
target_dir='e:\\workspace\\data\\'
con = sqlite3.connect(target_dir+db_name)
cur=con.cursor()
select_str='''select  capital.代码 as code,quote.close as close, quote.close*capital.股份总数/10000 as market_cap,     
    balance.负债合计/balance.资产总计 as debt_ratio,                                                        
    profit.归属于母公司所有者的净利润/balance.'所有者权益(或股东权益)合计' as roe,            
    profit.归属于母公司所有者的净利润/balance.'负债和所有者权益(或股东权益)总计' as roa ,                  
    quote.close*capital.股份总数/profit.归属于母公司所有者的净利润 as pe,                                   
    quote.close*capital.股份总数/balance.资产总计  as pb                                                     
    from profile,capital,quote,balance,profit                                                               
    where  capital.代码=300001   
    and profile.代码=capital.代码  
    and profile.代码=quote.code   
    and profile.代码=balance.代码
    and profile.代码=profit.代码  
    and quote.date='20140523'     
    and balance.报告日期='20131231'      
    and profit.报告日期='20131231';'''
cur.execute(select_str)
print(cur.fetchall())

In my point of view ,cod1 is equal to code2 logically ,there is no difference between them. Is there difference between

where  capital.代码='300001'  
and profile.代码='300001'  
and quote.code='300001'  
and balance.代码='300001'
and profit.代码='300001'

and

where  capital.代码=300001   
and profile.代码=capital.代码  
and profile.代码=quote.code   
and profile.代码=balance.代码
and profile.代码=profit.代码 

??

when i quote the code and get the right result ,so the reason of problem remain unknown.

Answer 1

I haven't downloaded the database since it's about 250 megabytes, and that's far too much to download to answer a simple question. If you want people to download the database, I suggest creating a small database with just a few records, the ones in your question.

Now for the actual answer: when you put quotes around the number 300001, you're searching for a string. When you omit the quotes, you're searching for an integer. The SQLite manual says that integers will never compare equal to strings (SQLite calls it a TEXT value):

An INTEGER or REAL value is less than any TEXT or BLOB value.

So your WHERE clause is never comparing equal when you search for an integer, because the actual value is a string. That's why you have to put quotes around "300001" is working.

UPDATE: The original version of my answer said "the actual value is an integer", which was backwards. After looking at the SQLite3 database that the OP posted, I see that the "代码" fields (for those who don't read Chinese, that translates roughly as "source code") are defined as TEXT fields in his schema. So I've edited my answer to swap integers and strings.

UPDATE 2: It seems, from the screenshots you've posted of sqlitemanager, that it is converting the integer values in your query to strings, because it knows that the SQL schema says that the 代码 field is a TEXT field and so it is correcting your query for you. (Which it should not do -- the number 1 is NOT the same as the string "1" and the one should NOT be converted into the other unless the user specifically asks for that conversion, but that's a rant about bad software design, which doesn't help answer your question.) So this "feature" of sqlitemanager (that's really a bug) is hiding the problem in your query, but the pysqlite3 library does not auto-convert data types and so it is exposing the problem.

Regardless of whether sqlitemanager's behavior is the right thing or not, the solution to your problem is very simple: make sure that when you're searching for a value in a TEXT field such as 代码, that you give it a search value that is a text string, enclosed in quotes. That will work everywhere, in both Python and in sqlitemanager, and so that's the way you want to write your queries.