On Sunday, May 29, 2016 at 12:05:52 AM UTC-4, Sammy wrote:
>
> I am a python/web2py newbie. I am trying to create a user friendly web
> search form for clients that connects to our existing Oracle DB and
> executes a query based on search criteria put in the form by users.
>
> For example, user will fill out the form saying first_name = "John" ,
> last_name="Smith" and when they submit, it will output for them all the
> information in the database for John Smith.
>
> I am using DAL to connect to the existing external Oracle DB
>
> my_db = DAL('oracle://username/password@Database')
>
> I am using simple FORM to create the form as I could not figure out how to
> use SQLFORM etc. because those seem to want me to create/define tables
> which I do not want to do as the table already exists and which do not want
> to modify. This is purely a query program.
>
Creating/modifying tables (i.e., "migrations") is an optional function of
the DAL. If you have existing tables that you don't want to modify, just
disable migrations:
my_db = DAL('oracle://username/password@Database', migrate_enabled=False)
When you call db.define_table, you are simply creating a Python model of
the database table, which the DAL will use to generate queries for selects,
inserts, updates, and deletes. You really should use the DAL and SQLFORM
rather than attempt to do everything manually.
> def next():
> sql = "SELECT * from Table 1 where field1 = session.first_name and
> field2 = session.last_name "
>
Not a good idea to generate your own SQL expressions by simply including
user input, as you become vulnerable to SQL injection attacks. Also, you
cannot use Python variables (e.g., session.first_name) directly in a SQL
expression -- you have to use Python string formatting to insert the actual
values of those Python variables.
> This works fine except that output is in this format:
>
> [(field1:value1,field2:value2)] etc.
>
Yes, if you just use db.executesql() to execute a custom SQL statement, you
get back a list of tuples from the database driver (the DAL does no further
processing, as it has no model of the data and therefore doesn't know what
to do with the data).
> What is the easiest way to output this in a html table output?
>
You would have to use the web2py view functionality to programmatically
build the table by iterating over the record tuples in the list. But don't
bother -- just create a DAL model and you can take advantage of all the
built-in functionality for generating forms, tables, and grids.
Anthony
--
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 the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
