Output format of an SQL query

[Sammy]

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.

My display_form is something like this

def display_form():
    form = FORM('First Name' INPUT(_name='first_name'),

                           'Last Name' INPUT (_name='last_name'),    
                INPUT(_type='submit'))

if form.accepts(request,session):

session.first_name = request.vars.first_name

session.last_name = request.vars.last_name

redirect(URL('next'))

return dict (form=form)

def next():
    sql = "SELECT * from Table 1  where field1 = session.first_name and field2 = session.last_name "
   
    rows = my_db.executesql(sql)
    return locals()

Then the next.html is

{{extend 'layout.html'}}
<h1>Here is your query results.</h1>
{{=rows}}

This works fine except that output is in this format:

[(field1:value1,field2:value2)] etc.

What is the easiest way to output this in a html table output?

I would also like to provide xls output option.

Thanks.

[Anthony]

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 (I won't go into details, as you shouldn't be doing this anyway).
 

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

[Sammy]

Thank you, Anthony. This is very helpful. Just to confirm though as I don't want to mess up my existing table, when using db.define_table, it just creates a "dummy" table within web2py but actual query is done on the real Table? I don't want to download all the data into the dummy table as there are millions of rows and constantly changing. 

[Anthony]

db.define_table generates a Table object. This is merely a model of the table in the database (i.e., it includes the field names and types as well as other attributes, such as whether null and non-unique values are allowed, etc.). The Table object contains no data at all -- it is merely used by the DAL in order to construct queries. All data remains in the database, and you only retrieve data when you call .select(), in which case you get back a Rows object.

Anthony

[Sammy]

TVM. I will try this.

[Sammy]

One more question. Do I have to call the Table name same in db.define_table as the real table name or it does not matter?

[Anthony]

db.define_table creates a model of the database table so the DAL can generate proper SQL queries to interact with the table -- so naturally the model must have the correct table and field names, otherwise the DAL would generate SQL queries referring to non-existent tables and fields. If for some reason you want to use different table and field names in your model, you can use the "rname" argument to db.define_table and Field() to specify the "real" name of the table/field (i.e., the name used by the database). This is useful if the real name used in the database is not a valid Python identifier.

Anthony

[Sammy]

I am still missing something.

I now have this in model

my_db = DAL('oracle://username/password@Database', migrate_enabled=False)

my_db.define_table('Table1', Field('field1'),

                             Field('field2'),

                             Field('Field3'),

                   )

# The above does not includes all the fields available in the real Table, just a subset,but with real field names  

My controller has this

def display_form():

    myquery = (my_db.Table1.field1 == 'Apple')

    myset = my_db(myquery)

    rows = myset.select()

    return locals()

And my view is simply

{{=rows}}

When I click on the link, I get this error:

<class 'cx_Oracle.DatabaseError'> ORA-00942: table or view does not exist

I was expecting to get query result matching Field1 = apple by querying the real Oracle DB.

What am I doing wrong?

[Dave S]

Do yu need to use the fake_migrate=True option?  See
<URL:http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Migrations>

/dps

[Sammy]

Just tried adding fake_migrate=True option

Did not help. Still same error.

[Sammy]

Actually the issue seems to be that I need to connect to DB using account that has r/w access. Now I am getting this error:

<class 'cx_Oracle.DatabaseError'> ORA-00904: "Table1"."ID": invalid identifier

Looking into that. Let me know if anyone knows the reason. Thanks.

[Anthony]

Please read the documentation on legacy databases: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Legacy-databases-and-keyed-tables

[Anthony]

On Wednesday, June 1, 2016 at 3:53:51 PM UTC-4, Dave S wrote:

Do yu need to use the fake_migrate=True option?  See
<URL:http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Migrations>

fake_migrate is only necessary if you have migrations enabled and you need the DAL migrations metadata to be made up-to-date with the current state of the database (based on the current model definitions) -- so the DAL doesn't attempt to make unnecessary migrations. If you have migrations disabled altogether, there is no need for this.

Anthony

[Sammy]

I will probably be picking this back up in a few weeks as I will be sidetracked for a while. I am just curious though why web2py requires one to define a table even if it already exists. e.g. if I have an Oracle client installed, all I need is a connection string. I don't need to define a model table locally before starting my sql queries. Why can't web2py do the same? This sounds like extra work.

[Dave S]

On Monday, June 6, 2016 at 1:34:00 PM UTC-7, Sammy wrote:

I will probably be picking this back up in a few weeks as I will be sidetracked for a while. I am just curious though why web2py requires one to define a table even if it already exists. e.g. if I have an Oracle client installed, all I need is a connection string. I don't need to define a model table locally before starting my sql queries. Why can't web2py do the same? This sounds like extra work.

If you're only going to be using the DAL's "executesql" function, the model tables would be unnecessary.  But the DAL provides a lot more, including the select() function.  To use the select() function, the DAL needs to know the structure of the tables in the database.  That's what the model files tell it.  The DAL also provides a way to create tables in the database, and again uses the model files for that.  Not needed for existing tables, but for many projects web2py is used for, the database doesn't exist until we use web2py to create it.

/dps


 

[Sammy]

Oh sure, I understand the importance of define.table as a way to create table if it does not exist already. But for existing tables, how is it that an oracle client is able to understand the structure of a table to do queries without further instructions but for DAL you need to explicitly define it?

[Niphlod]

the oracle client really doesn't know anything about your database, it just spits what oracle has to tell.

DAL is an "Abstraction", so to "abstract" a model, it needs the definition (the brain that designed the oracle table had it, it just translated to SQL rather than python)

But let's put it in another way:

let your oracle client insert in a table a row and fetch the newly inserted id. Using DAL, you can just issue this

newly_inserted = db.table.insert(datetime_field=request.now, integer_field=2)

or, fetch all rows pertaining to the current year, then for each line print a different column. Using DAL, you can just issue this

results = db(db.table.datetime_field.year() == 2016).select()
for row in results:
     print row.integer_field

Go ahead and as an exercise, use your oracle client: the goal is use less lines (and characters) than the aforementioned snippets (feel free to count the define_table statement too). 

Then count how many times you do CRUD operations in your app. Then multiply by the difference.

If the result is less than 100 lines, use db.executesql(). And you get no automatic representation of the result in a nice table, no grid, no smartgrid, and you locked your app's code to run on Oracle only.

If you need one of the above, or the result is more than 100 lines, join the "it's better to define a model for a database" world ^_^

[Anthony]

On Monday, June 6, 2016 at 5:17:34 PM UTC-4, Sammy wrote:

Oh sure, I understand the importance of define.table as a way to create table if it does not exist already. But for existing tables, how is it that an oracle client is able to understand the structure of a table to do queries without further instructions but for DAL you need to explicitly define it?

If you want to manually write your own SQL queries (being sure to avoid SQL injection) and then get back a standard database driver result set (i.e., a list of tuples), then there is no need to use the DAL at all. Just use the database driver, make a connection, and do things the way you want.

However, have a look at some of the DAL features: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer. Then there is SQLFORM, SQLTABLE, and SQLFORM.grid, which make use of the DAL. If any of those features appeal to you, you might find it worth the effort to define DAL models representing your database tables. Without such models, the DAL wouldn't know enough about your database schema to enable all of that functionality. Futhermore, many attributes of the DAL models go beyond merely representing the database schema and provide additional information/functionality regarding your data models (e.g., validators, representation functions, form and grid labels, computation functions, file uploads, etc.).

Also, note that if you are starting with an existing database and need help constructing DAL model code based on your current database schema, you can use/adapt one of the scripts that come with web2py, such as https://github.com/web2py/web2py/blob/master/scripts/extract_oracle_models.py.

Anthony

[Sammy]

Thanks for your responses. I certainly want to take advantage of SQLFORM.grid etc. and I don't mind defining tables. However, I am getting errors.

Initially, I was getting the error previously mentioned, i.e.  <class 'cx_Oracle.DatabaseError'> ORA-00904: "Table1"."ID": invalid identifier.

Using the link for legacy DB provided by Anthony, I added key field using Field('....', 'id'), but now I am getting this error: <type 'exceptions.ValueError'> invalid literal for long() with base 10:'..'

What am I doing wrong?

I have a few questions that might help me in this-

1) When they say "legacy DB" do they mean all database created outside of web2py? 

2) Do I need to define ALL the fields available in the real Table or can I get away with defining just the fields that I am interested in? 

3) Do I need to specify "type" for each field to match real field type? What if there is no one to one mapping available? 

[Anthony]

On Tuesday, June 7, 2016 at 11:36:43 AM UTC-4, Sammy wrote:

Thanks for your responses. I certainly want to take advantage of SQLFORM.grid etc. and I don't mind defining tables. However, I am getting errors.

Initially, I was getting the error previously mentioned, i.e.  <class 'cx_Oracle.DatabaseError'> ORA-00904: "Table1"."ID": invalid identifier.

Using the link for legacy DB provided by Anthony, I added key field using Field('....', 'id'), but now I am getting this error: <type 'exceptions.ValueError'> invalid literal for long() with base 10:'..'

What am I doing wrong?

In web2py, the "id" field must be an auto-incrementing integer field. If this is not the case for your table (and you cannot change it), then you must instead use the "primarykey" argument (see http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Legacy-databases-and-keyed-tables), though this entails some limitations.
 

I have a few questions that might help me in this-

1) When they say "legacy DB" do they mean all database created outside of web2py?

Yes, though a more important distinction is whether the database tables use an auto-incrementing integer field as the primary key (and whether this is used for all references). See link above.
 

2) Do I need to define ALL the fields available in the real Table or can I get away with defining just the fields that I am interested in? 

No, just the fields you are using.
 

3) Do I need to specify "type" for each field to match real field type? What if there is no one to one mapping available? 

Ideally. At least provide a type consistent with the data that will be returned by the database driver (e.g., don't tell web2py to expect floats from a string field).

Anthony

[Sammy]

Actually I did try using primarykey also, but the error is still the same:  <type 'exceptions.ValueError'> invalid literal for long() with base 10: '....'

Any idea?

[Anthony]

Is the ID field in your database an integer field?

[Sammy]

Thanks, Anthony, that was the problem! I assumed this was an integer (this is a 3rd party product), but looks like it is actually  varchar when I checked after your latest query. Once I removed type as 'integer' it worked.

I will let you know if I have any follow up questions. Thank you!