Table of Contents

  1. GnrSql Overview
    1. GnrSqlDb
    2. Database model
    3. Accessing the db
    4. Getting data
  2. Quick Start
    1. Use Genro on your SQL db
      1. Connecting to database
      2. Reading database Model
      3. Build the Model objects hierarchy
      4. Explore the model
      5. Selection
      6. Record
      7. Surfing data through relations
  3. Database model
    1. Model Source
      1. Writing a DbModelSrc in Python
        1. Adding package
        2. Adding tables
        3. Adding columns
        4. Adding relations
        5. Adding indexes
      2. Python vs. XML
    2. Model Object
  4. Query
      1. parameter: columns
      2. parameter where
      3. parameter relationDict
  5. Records
    1. record come bag
    2. campi primari
    3. campi in relazione ascendente
    4. campi in relazione discendente
    5. uso formule
    6. browsing automatico da un record nelle relazioni
    7. creazione nuovi record
    8. salvataggio di record
    9. modifica
    10. cancellazione
    11. trigger
    12. gestione record di transazione
  6. Creation and editing
    1. Esempi pratici

Query

SqlQuery? object represent the way in which data can be extracted from a db. You can get data with these methods of SqlQuery?: selection, with return SqlSelection?, fetch, count, cursor and serverCursor.

The query method starts from the point of view of a table, that is the most important of the SQL FROM clause. Don't warry about JOIN and other tables, GnrSqlDb cares about everything so just choose one of them.

You can also call query method from the SqlTable? or you can call it from the GnrSqlDb instance itself passing the path to table as mandatory parameter 

#from db
query = db.query('video.movie',
                  columns = '*',
                  where = '$title=:title',
                  sqlparams ={'title':'Scoop'})

#from table equivalent
movie = db.table('video.movie')
query = movie.query(columns = '*',
                    where = '$title=:title',
                    sqlparams ={'title':'Scoop'})

parameter: columns

It's an optional parameter and it's composed by a special string that substitutes

what's between SELECT and FROM in SQL queries. The default value is the char * that implies a selection of every table's columns. Columns may be expressed with different sintaxes:

  • $column_name
  • $variable_name
  • @relation.relatedColumn

Columns also supports the use of the SQL clause AS and the use of database functions:

  • sum($column_name) AS total

parameter where

Where is another optional parameter composed by a special string. It's syntax is very similar to the common WHERE clause of SQL queries. It specifies conditions on valid values of selected columns. It refer to columns with the same notation used in the parameter columns.

Except for the column and parameter sintax, the WHERE string is left as is: so any WHERE clause valid for the current database implementation can be used.

If any parameter is used in the conditions it must be expressed with the :word syntax. (i.e. $age>:age_limit).

parameter relationDict

If any variable name has been used in columns or where to indicate a particular column, these names must be linked with the table's name using the dictionary relationDict. 

bd.selection(columns='$a, $t' relationDict={'a':'@author_id.name', 't':'$title'}, limit=30)