Web2PY MySQL DB connection and data retrieval

[Vj]

All,

A new web2py & python user here.

I am trying to build a sample webapp using web2py. My plan is to use MySQL DB (tables already created with data). I want to retrieve data from the table from MySQL and display on the home page. I have errors when doing that -

<class 'gluon.contrib.pymysql.err.InternalError'> (1050, u"Table 'user_details' already exists")

My Config files are below

db.py

# -*- coding: utf-8 -*-
if not request.env.web2py_runtime_gae:
    ## if NOT running on Google App Engine use SQLite or other DB
    ##db = DAL('sqlite://storage.sqlite',pool_size=1,check_reserved=['all'])
    db = DAL('mysql://xxxxxxx',pool_size=1,check_reserved=['all'])
else:
    ## connect to Google BigTable (optional 'google:datastore://namespace')
    db = DAL('google:datastore')
    ## store sessions and tickets there
    session.connect(request, response, db=db)

response.generic_patterns = ['*'] if request.is_local else []

from gluon.tools import Auth, Crud, Service, PluginManager, prettydate
auth = Auth(db)
crud, service, plugins = Crud(db), Service(), PluginManager()

## create all tables needed by auth if not custom tables
auth.define_tables(username=False, signature=False)

db.define_table('user_details',
    Field('user_id', 'text'),
    Field('first_name', 'text'),
    Field('last_name', 'text'),
    Field('city', 'text'),
    Field('user_st', 'text'),migrate=True)

I have the table user_details created already in MySQL. I want to display the information of all the records from the table.

My home page code is 

{{ rows = db(db.user_details).select() }}
{{if len(rows):}}
<ul>
{{ for r in rows: }}
  <li>

        {{=r.name}}
   
  </li>
{{pass}}
</ul>
{{pass}}


I am not able to find out the issue. Any help appreciated. Thanks.


[Niphlod]

if you don't want web2py to create the table, you need to do

db.define_table('tablename',
   Field()
   .....
   migrate=False
)

Read here for the gotchas.... http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Legacy-databases-and-keyed-tables

[Vj]

Got it. It worked now. Thanks.

[Sankhajit Das]

hey. I have created a mysql database in my web2py app.

now i want to create another sqlite database such that whatever data i enter 

in sqlite database. it automatically gets copied in mysql database.

Pls somebody help me do ds with full code.

  

[Dave S]

To do it with web2py,  you open a connection to both databases, select from one and add to the other.  See

<URL: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Inserting-and-updating-from-a-dictionary>

If the data is coming from a form, you can trigger the copy by using an after-insert callback.

<URL:http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#callbacks-on-record-insert--delete-and-update>

If you don't need to do the copying ASAP, but prefer to batch the job, then the scheduler and export as CSV might be more convenient.

<URL:http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#CSV--one-Table-at-a-time->

<URL:http://web2py.com/books/default/chapter/29/04/the-core#web2py-Scheduler>

Good luck!

/dps

[Sankhajit Das]

how can we delete data automatically from a table in database after clicking submit button of a form in web2py

[黄祥]

perhaps you can put delete() or truncate() function to delete data in database table in form accepts() or form accepted() function

best regards,

stifan

[Sankhajit Das]

pls help me with the full code.. m unable to do it
 

[Sankhajit Das]

how can i store all the sqlite database in a variable????? smbdy help me with full code pls

[Sankhajit Das]

On Thursday, January 19, 2017 at 10:11:53 AM UTC+5:30, Sankhajit Das wrote:

how can i store all the sqlite database in a variable????? smbdy help me with full code pls

or how can i write all the sqlite data in a text file??????????/

[Dave S]

Have you looked at the link for CSV export?

/dps