Problems with SQLite data types and the table_info() method

[sean-duffy]

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.

[zarino]

Could you show me the python code you used to create the table and the rows in the first place?

[sean-duffy]

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})

[zarino]

What happens if you run:

print scraperwiki.sql.select('sql from sqlite_master')[0]['sql']

(assuming you only have one table)

[sean-duffy]

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?

[ghost]

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:
#24 (comment)