Re: Simple Postgres Exercise and ValueError: invalid literal for int() with base 10: 'SELECT'

[Niphlod]

the code you posted (of course, correctly spaced in the controller function) works without problems.
Check your database/sql.log for details about the table being effectively created....

On Wednesday, May 22, 2013 8:14:41 PM UTC+2, ewon...@gmail.com wrote:

What I really wanted to do was connect to an existing postgres database and read/display the data, but I kept getting this error: ValueError: invalid literal for int() with base 10: 'SELECT'

 

I've pared down to some stupidly simple code that works with sqlite, but when I change to use my postgres database, I still get that error! 

This is what I have:

db.py: Just create a table called mydata with one field of type string.  For this example I'm trying, table mydata does not yet exist in my postgres db.

 

db = DAL('postgres://dbuser:dbuser@XXX.XX.XXX.XX/erinstest',pool_size=1,check_reserved=['all'])                                                      
#db = DAL('sqlite://storage.sqlite',pool_size=1,check_reserved=['all'])                                                      
db.define_table('mydata',                                                      
Field('det_data_type', 'string', length=32))

 

default.py: Insert something into the mydata table and query/return everything from the table.

 

def index():      
response.flash = T("Welcome to web2py!")     

db.mydata.insert(det_data_type="type1") 
pkts = db().select(db.mydata.ALL)      
return dict(pkts=pkts)

 

 

 

default/index.html: Display each of the results from the query of the mydata table.

{{ for pkt in pkts: }}             
{{=pkt.id}}             
{{=pkt.det_data_type}}             
{{pass}}  

 

When I run this, the mydata table is created in my postgres database (so I assume my connection is working at least somewhat correctly), but nothing is inserted into it and the ValueError comes up.  After doing this, I also tried inserting something into the table (outside of web2py) and then commented out the line in default.py that inserts into the table to see if I could just display what was in mydata table.  Same error.  What am I doing wrong?

 

I am using web2py v2.4.6 and postgres 8.4.13.

 

 

[ewon...@gmail.com]

The sql.log only said that the table was effectively created (it was), just couldn't read or write to it after. 

 

Are you using psycopg2?  I was just using the pg8000 that comes with web2py, however someone informed me that web2py is broken as shipped (!) and sure enough, after installing psycopg2 my code worked.  I can't believe they would ship a product that does not work...and this is such basic functionality!  If this is true, they really need to a) Not include pg8000 with their next release and b) Put some kind of blaring warning message with the current release that you NEED psycopg2 to be able to work with postgres.  Sure wasted my time!