sensiblecodeio
Problems with SQLite data types and the table_info() method
I was creating a scraper on the ScraperWiki website and wanted the table containing its data to be ordered by the values of a column of integers. However, both the 'View in a table' and 'Query with SQL' tools are only sorting the data alphanumerically (i.e. the values starting with 1 first, then the values starting with 2 &c) rather than by their numeric values.
This led me to believe that for some reason this column is being given a string type despite containing only integers, so I tried to use:
scraperwiki.sql.table_info('swdata')
which I found in the readme but this is giving me an attribute error.
Could you show me the python code you used to create the table and the rows in the first place?
Sure, I'm just using the save() method to store each pair of values.
for i, subtext in zip([i for i in range(60) if i % 2 != 0], subtexts):
title = titles[i].cssselect('a')[0].text_content()
if 'points' in subtext.text_content():
score_text = subtext.cssselect('span')[0].text_content()
score = int(score_text[:score_text.find('p'):])
else:
score = 0
scraperwiki.sql.save(['title'], {'title': title, 'score': score})
What happens if you run:
print scraperwiki.sql.select('sql from sqlite_master')[0]['sql']
(assuming you only have one table)
I get this:
CREATE TABLE `swdata` (
`score` text,
`title` text)
So indeed the problem must be the score column taking on a 'text' type. Is the table_info() method no longer a part of the library?
No, there's no table_info function any more.
On Fri, Jul 05, 2013 at 11:14:20AM -0700, Sean Duffy wrote:
I get this:
REATE TABLE `swdata` ( `score` text , `title` text)So indeed the problem must be the score column taking on a 'text' type. Is the table_info() method no longer a part of the library?
Reply to this email directly or view it on GitHub: https://github.com/scraperwiki/scraperwiki-python/issues/24#issuecomment-20532171
