how to iterate through sqlite data and display on web2py

[Maurice Waka]

I have several lists in sqlite rows as follows:

ROWID  x
  1    ['123', '1234', '12345',]
  2    ['abc', 'abcd', 'abcde',]
  3    ['1a2b3c', '1a2b3c4d', '1a2b3c4d5e',]

I would like to iterate over these items in a game that matches them with a user input, say when a user prints 123, it prints out the answer or gives a boolean value. The only problem is that when i print our items in row[1] or row[2] i get a none value, but is works well with row[0].

This is part of my code on the sqlite section.

def types():
    location = ""
    conn = sqlite3.connect("types.db")
    c = conn.cursor()
    c.execute('select * from types ORDER by X')    
    for rowid,X in c.execute("select rowid,X from types order by X"):
      return row[:]

[Anthony]

On Tuesday, September 9, 2014 8:08:00 AM UTC-4, Maurice Waka wrote:

I have several lists in sqlite rows as follows:

ROWID  x
  1    ['123', '1234', '12345',]
  2    ['abc', 'abcd', 'abcde',]
  3    ['1a2b3c', '1a2b3c4d', '1a2b3c4d5e',]

How are you storing the lists in a single SQLite column? Did you convert Python lists to strings?
 

def types():
    location = ""
    conn = sqlite3.connect("types.db")
    c = conn.cursor()
    c.execute('select * from types ORDER by X')    
    for rowid,X in c.execute("select rowid,X from types order by X"):
      return row[:]

Given that the identifier "row" has not been defined, the final line should raise an exception. Also, you don't want to return in the for loop, as that will terminate the function on the first pass through the loop.

Is this in a web2py app? If so, I would recommend using the DAL and store your lists in a list:string field. As far as looping and returning values, it's hard to say without more details regarding what you are trying to do. Are you just trying to display all the rows in an HTML page?

Anthony

[Maurice Waka]

Thanks for the input. Yes it is part of a web2py app game.

I have about 1000 rows, but when a user types in the keyword(stored in any of the rows) i should get a boolean answer which for now displays the row(different code on this)

Regards

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/dNtVIOucH9Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Dr Maurice Waka, MBCHB.

Nairobi

[Anthony]

On Tuesday, September 9, 2014 8:40:04 AM UTC-4, Maurice Waka wrote:

Thanks for the input. Yes it is part of a web2py app game.

I have about 1000 rows, but when a user types in the keyword(stored in any of the rows) i should get a boolean answer which for now displays the row(different code on this)

OK, but how are the rows stored in SQLite? Are they just strings, like "['123', '1234', '12345', ]"? If so, you can do a query searching for the string "'[keyword]'," within each row to return rows with a matching keyword. Again, I would recommend using the DAL with a list:string field:

db.define_table('keywords',
    Field('x', 'list:string'))

This allows you to insert and extract actual Python lists from the field, though the lists will be stored in the database as a string in the form "|item1|item2|item3|". So, to find rows that match a keyword, you would just do:

keyword = '123' # in reality, this is obtained via user input
match_row = db(db.keywords.x.contains('|%s|' % keyword)).select().first()

Your boolean test would then simply be "if match_row:". The list of keywords in the matching row would be in match_row.x, which would be an actual Python list rather than a string representation of a list.

Anthony

[Maurice Waka]

Hey thanks alot! let me work on this then.

Kind regards

--

Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/dNtVIOucH9Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Maurice Waka]

Hi

1. I note that the code:
match_row = db(db.keywords.x.contains('|%s|' % keyword)).select().first(). This picks the first item in he list. How about a random search through the list without using select().first()?



2. In my code:


 def types:


    db = DAL('sqlite.storage.db')


    db.define_table('types'


                    Field('body'))


    rows = db(db.types.body.id>0)select()


    for item in row:


        item = item


        return item.


I GET AN ERROR: NoneType item not iterable. I want the user to put in any data e.g. '123' and if boolean(True) it prints out the answer.I am using this code on a ython module imported to web2py and not in the controller. In my view: {{=item}}


[Anthony]

On Wednesday, September 17, 2014 9:12:11 AM UTC-4, Maurice Waka wrote:

Hi

1. I note that the code:
match_row = db(db.keywords.x.contains('|%s|' % keyword)).select().first(). This picks the first item in he list. How about a random search through the list without using select().first()?


db(db.keywords.x.contains('|%s|' % keyword)).select(orderby='<random>').first()

 

2. In my code:


 def types:


    db = DAL('sqlite.storage.db')


    db.define_table('types'


                    Field('body'))


    rows = db(db.types.body.id>0)select()


    for item in row:


        item = item


        return item.


I GET AN ERROR: NoneType item not iterable. I want the user to put in any data e.g. '123' and if boolean(True) it prints out the answer.I am using this code on a ython module imported to web2py and not in the controller. In my view: {{=item}}


A few problems:

• Should be db.types.id >0, not db.types.body.id > 0.
• If you have "return item" in your for loop, it will simply return during the first iteration of the loop. Instead, you should just return the Rows object and let the view iterate and display all the items.
• If this code is in a module, the return value of the function will not be available in a view. When a controller function returns a dict, the keys in the dict become global variables in the associated view. So, if you want to pass something from a function in a module to a view, the module function should be called by the controller function (alternatively, the view can directly import and call the module function, but that pattern is discouraged -- try to keep the views to presentation logic).

It might help if you spend a little more time with the web2py documentation to better understand how everything works together.

Anthony