In web2py, you could import MySQLdb and make SQL queries directly, but you
would lose out on a lot of the time-saving features that web2py offers, such
as automatic form generation for database tables. Instead, you would
typically use web2py's DAL (Database Abstraction Layer), which provides a
higher-level interface to your database. For example, if you have a
"product" table with fields "name", "price", and "quantity", you might
define it in web2py's DAL like this:
db = DAL("mysql://user:password@host/mydatabase")
db.define_table('product',
Field('name', 'string'),
Field('price', 'double'),
Field('quantity', 'integer')
)
(Note that an "id" field is predefined for you if you don't explicitly
define one)
And then you could do queries like:
product = db.product(123) # get product 123, or return None if it does not
exist
print product.name, product.price
# Update an existing product
product.update_record(price=2.50)
# Insert a new product
db.product.insert(name="Oranges", price=1.23, quanitty=50)
If you want to take advantage of these features with an existing mysql
database, you can use the extract_mysql_models script described above to
read an existing mysql database and generate the corresponding DAL database
structure.
See chapter 4 of the web2py book for more information about web2py's
database layer:
http://web2py.com/book/default/chapter/06
Cheers,
Kevin
