Syntax:
CREATE | REPLACE TABLE table-name AS select-statement TRANSFORM: Gython-code-line [Gython-code-line]* [GLOBALS: Global-Gython-code-line [Global-Gython-code-line]* ] [KEEP | DROP column-name-1[, column-name 2[, ...]]
In GDBase it is possible to combine SQL commands with Gython code. This can be done with the CREATE TABLE ... TRANSFORM command. The functionality of this command is similar to the trans procedure, but the approach described here enables the integration of sql and Gython data processing.
Explanation:
The Gython code following the TRANSFORM and GLOBALS keywords should be indented to the right.
The Gython code in the TRANSFORM part is executed once for every row of the table-name table.
The Gython code in the GLOBALS part is executed once before the table is processed.
The columns following the KEEP or DROP keywords can be specified verbatim or with regular expressions.
Example 43.1. Basic syntax.
# input table:
table 'TAB1':
format c DATE 'yyyy-MM-dd'
a b c d
'aaa' 10 '2008-12-04' 10
'bbb' 40 '2007-11-06' 20
'ccc' -20 '2000-09-11' 45
sql:
REPLACE TABLE TAB2 AS
SELECT *, a || b AS con FROM TAB1
TRANSFORM: # the python part; pay attention to indents !!!
e = b * 10 # standard python operation
f = f1(b,d) # execution of a user-defined function
sum = sum + b # agregation
g = sum # writing the value of global variable to the output column
h = sin(b) # execution of a function from the imported package 'math'
i = c.weekday()
j = c.year
GLOBALS:
sum = 0 # initialization of a global variable
from math import * # importing packages
from datetime import *
def f1(arg1,arg2): # defining a user function
if arg1 == arg2:
f1result = 1
else:
f1result = 0
return f1result
DROP a, b, c # dropping some of the columns
trans None <- 'TAB2':
print sum, d, e ,f ,g ,h, i, j, con, sum
Output:
10 10 100 1 10 -0.5440211108893698 3 2008 aaa10 10 50 20 400 0 50 0.7451131604793488 1 2007 bbb40 50 30 45 -200 0 30 -0.9129452507276277 0 2000 ccc-20 30
The following packages can be imported in the GLOBALS part:
| __builtin__ |
| __main__ |
| _ast |
| _codecs |
| _md5 |
| _random |
| _sha |
| _sre |
| _struct |
| _symtable |
| _testcapi |
| _types |
| _weakref |
| array |
| cStringIO |
| cmath |
| collections |
| datetime |
| errno |
| exceptions |
| gc |
| imp |
| itertools |
| marshal |
| math |
| operator |
| posix |
| pwd |
| signal |
| strop |
| sys |
| thread |
| time |
| unicodedata |
| zipimport |
To access the columns chosen in the SELECT statement simply use their names as if they were ordinary Python variables. Any other variables created in the TRANSFORM part will also appear as columns of the output table. Moreover, the variables created in the GLOBALS part will appear in the output table as well. The DROP clause can be used to prevent some of the Python variables or the variables from the SELECT statement from appearing in the the output table. Alternatively, the KEEP clause can be used to indicate, which variables should appear in the output table.
The __vars__ dictionary provides a way of accessing all locally defined variables, which are available at the moment the function is used in the code. For example __vars__['x'] will refer to the value of the variable x, and __vars__['y'] = 15 will assign the value 15 to the variable y or create this variable with the value 15 if a variable called y does not exist.
Example 43.2. Application of the __vars__ function
The main application of the __vars__ function is to create or access variables with names created in Gython code.
for i in range(3)
__vars__['x_%d'%1] = 1
This code will create the variables x_1, x_2, x_3 with the values 1, 2, 3 respectively.
See also the table transposition example below for an illustration how the __vars__ function is used inside the TRANSFORM block.
Sometimes when transforming a table it may be necessary to skip some of the processed rows. This can be achieved with the __skipRow__ variable. Specifically, __skipRow__ = 1 will result in current row not being written to the output table, while __skipRow__ = 0 will have the opposite efffect.
See the examples choosing the latest id of a day, transposition using groups and transposition using the __save__ function for an illustration how __skipRow__ can bu used inside the TRANSFORM block.
The CREATE TABLE ... TRANSFORM enables group processing with the ORDER BY statement:
Example 43.3. Processing in groups, example 1.
sql:
REPLACE TABLE TAB2 AS
SELECT purpose FROM german_credit
ORDER BY purpose
TRANSFORM:
gr_begin = __group__.start(0) # indicator of group beginning
gr_end = __group__.end(0) # indicator of group end
if gr_begin == 1:
tmp = 1
else:
tmp = tmp + 1
if gr_end == 1:
how_many_1 = tmp
GLOBALS:
tmp = 0
DROP tmp
sql:
REPLACE TABLE TAB3 AS
SELECT * FROM TAB2
ORDER BY purpose, how_many_1 desc
TRANSFORM:
if gr_end == 1:
tmp = how_many_1
how_many_2 = tmp
GLOBALS:
tmp = 0
DROP how_many_1, tmp
sql TAB3_out:
SELECT * FROM 'TAB3'
ORDER BY gr_begin DESC, purpose ASC
LIMIT 10
print TAB3_out
Output:
purpose | gr_begin | gr_end | how_many_2 | +-------------------+----------+--------+------------+-- business | 1 | 0 | 97 | domestic appliance | 1 | 0 | 12 | education | 1 | 0 | 50 | furniture/equipment | 1 | 0 | 181 | new car | 1 | 0 | 234 | other | 1 | 0 | 12 | radio/tv | 1 | 0 | 280 | repairs | 1 | 0 | 22 | retraining | 1 | 0 | 9 | used car | 1 | 0 | 103 |
The use of ORDER BY creates a __group__ object, which has two methods: start([group_no]) and end([group_no]); group_no is indexed from 0 (0 corresponds to the group which is most general, internal, connected with first variable used in ORDER BY). If there is no group_no the method will return the number of the last, most detailed, external group.
Example 43.4. Processing in groups, example 2.
table 'TAB4':
a b c
1 1 1
1 1 2
1 2 3
2 1 1
2 1 2
2 2 3
sql:
REPLACE TABLE TAB5 AS
SELECT * FROM TAB4
ORDER BY a, b, c
TRANSFORM:
s2 = __group__.start(2)
e2 = __group__.end(2)
s1 = __group__.start(1)
e1 = __group__.end(1)
s0 = __group__.start(0)
e0 = __group__.end(0)
sql TAB5_out:
SELECT * FROM TAB5
print TAB5_out
Output:
a | b | c | s2 | e2 | s1 | e1 | s0 | e0 | +-+---+---+----+----+----+----+----+----+-- 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 | 1 | 2 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 2 | 3 | 1 | 1 | 1 | 1 | 0 | 1 | 2 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 0 | 2 | 1 | 2 | 1 | 1 | 0 | 1 | 0 | 0 | 2 | 2 | 3 | 1 | 1 | 1 | 1 | 0 | 1 |
The example below shows how easy it is to find the 'newest' information in a dataset.
Example 43.5. Choosing the latest id of a day.
table 'IDS':
format day DATE 'yyyy-MM-dd'
day id
'2005-03-21' 124345324
'2005-03-22' 3252352
'2005-03-23' 325235
'2005-03-23' 43252435
'2005-03-23' 33333333
'2005-03-23' 1111111111
'2005-03-24' 43333333
'2005-03-24' 34355555
'2005-03-24' 99999999
sql:
REPLACE TABLE IDS_last AS SELECT * FROM IDS
ORDER BY day, id
TRANSFORM:
if __group__.end(0):
__skipRow__ = 0
else:
__skipRow__ = 1
sql IDS_last_out:
SELECT * FROM IDS_last
print IDS_last_out
Output:
day | id | +----------+------------+-- 2005-03-21 | 124345324 | 2005-03-22 | 3252352 | 2005-03-23 | 1111111111 | 2005-03-24 | 99999999 |
Group processing can be used to perform transposition of data in a table, although there is no special function to realize it.
Example 43.6. Transposition
table 'clients':
client statistics value
'john' 'amount' 100000.0
'john' 'duration' 17.0
'john' 'packages' 3
'eve' 'amount' 5000.0
'eve' 'duration' 5.0
'eve' 'packages' 1
'peter' 'amount' 0.0
'peter' 'duration' 36.0
'peter' 'packages' 12
sql:
REPLACE TABLE clients2 AS
SELECT *
FROM clients
ORDER BY client
TRANSFORM:
if __group__.start():
names = []
values = []
names.append(statistics)
values.append( value )
if __group__.end():
for i in range(len(names)):
__vars__[names[i]] = values[i]
else:
__skipRow__ = 1
DROP statistics, i
sql s:
SELECT * FROM clients2
print s
Output:
client | value | amount | duration | packages | +------+-------+----------+----------+----------+-- eve | 1.0 | 5000.0 | 5.0 | 1.0 | john | 3.0 | 100000.0 | 17.0 | 3.0 | peter | 12.0 | 0.0 | 36.0 | 12.0 |
The __save__(['save-table-name']) function causes the current row from the input table to be immediately written to the table save-table-name. After this instruction the execution of Python code is resumed. If the function argument is omitted, the data is written to the table from the CREATE/REPLACE TABLE clause.
If the table save-table-name does not exist, it will be created, if it exists, it will be overwritten.
Example 43.7. The __save__() function.
table 'TAB1':
format c DATE 'yyyy-MM-dd'
a b c d
'aaa' 10 '2008-12-04' 10
'bbb' 40 '2007-11-06' 20
'ccc' -20 '2000-09-11' 45
sql:
REPLACE TABLE TAB6 AS
SELECT * FROM TAB1
TRANSFORM:
if a == 'aaa':
__save__()
a = a + 'bye'
sql a:
SELECT * FROM TAB6
print a
Output:
a | b | c | d | +------+-----+------------+----+-- aaa | 10 | 2008-12-04 | 10 | aaabye | 10 | 2008-12-04 | 10 | bbb | 40 | 2007-11-06 | 20 | ccc | -20 | 2000-09-11 | 45 |
This function can be used to transpose the data in a table, but in a different way than by group processing.
Example 43.8. Transposition
table 'clients2':
client amount duration packages
eve 5000.0 5.0 1.0
john 100000.0 17.0 3.0
peter 0.0 36.0 12.0
sql:
REPLACE TABLE clients AS SELECT * FROM clients2 TRANSFORM:
value = amount
statistics = 'amount'
__save__()
value = duration
statistics = 'duration'
__save__()
value = packages
statistics = 'packages'
__save__()
__skipRow__=1
KEEP client, value, statistics
sql s:
SELECT * FROM clients
print s
# the same code may be realized a little bit differently:
# sql:
# REPLACE TABLE clients AS SELECT * FROM clients2 TRANSFORM:
#
# for statistics in ['amount', 'duration', 'packages']:
# value = __vars__[statistics]
# __save__()
#
# __skipRow__=1
#
# KEEP client, value, statistics
Output:
client | value | statistics | +------+----------+------------+-- eve | 5000.0 | amount | eve | 5.0 | duration | eve | 1.0 | packages | john | 100000.0 | amount | john | 17.0 | duration | john | 3.0 | packages | peter | 0.0 | amount | peter | 36.0 | duration | peter | 12.0 | packages |
There is no special function for accessing values from previous rows but the example below shows how to deal with it.
Example 43.9. Getting values from prevoius rows.
table 'chf':
format When DATE 'yyyy-MM-dd'
When Exchange Diff
'2009-05-04' 2.9068 0.08
'2009-05-05' 2.8753 -1.08
'2009-05-06' 2.9134 1.33
'2009-05-07' 2.8649 -1.66
'2009-05-08' 2.8742 0.32
'2009-05-11' 2.9020 0.97
'2009-05-12' 2.9166 0.50
'2009-05-13' 2.9147 -0.07
'2009-05-14' 2.9700 1.90
'2009-05-15' 2.9768 0.23
sql:
REPLACE TABLE previous AS
SELECT * FROM chf
TRANSFORM:
Exchange_1 = prev_table[-1]
Exchange_2 = prev_table[-2]
Exchange_3 = prev_table[-3]
Exchange_4 = prev_table[-4]
Exchange_5 = prev_table[-5]
prev_table.append(Exchange)
del prev_table[0]
GLOBALS:
prev_table = [ None ] * 5 # the user can decide how far into the past to reach
sql previous_out:
select * from previous
print previous_out
Output:
When | Exchange | Diff | Exchange_1 | Exchange_2 | Exchange_3 | Exchange_4 | Exchange_5 | +----------+----------+-------+------------+------------+------------+------------+------------+-- 2009-05-04 | 2.9068 | 0.08 | None | None | None | None | None | 2009-05-05 | 2.8753 | -1.08 | 2.9068 | None | None | None | None | 2009-05-06 | 2.9134 | 1.33 | 2.8753 | 2.9068 | None | None | None | 2009-05-07 | 2.8649 | -1.66 | 2.9134 | 2.8753 | 2.9068 | None | None | 2009-05-08 | 2.8742 | 0.32 | 2.8649 | 2.9134 | 2.8753 | 2.9068 | None | 2009-05-11 | 2.902 | 0.97 | 2.8742 | 2.8649 | 2.9134 | 2.8753 | 2.9068 | 2009-05-12 | 2.9166 | 0.5 | 2.902 | 2.8742 | 2.8649 | 2.9134 | 2.8753 | 2009-05-13 | 2.9147 | -0.07 | 2.9166 | 2.902 | 2.8742 | 2.8649 | 2.9134 | 2009-05-14 | 2.97 | 1.9 | 2.9147 | 2.9166 | 2.902 | 2.8742 | 2.8649 | 2009-05-15 | 2.9768 | 0.23 | 2.97 | 2.9147 | 2.9166 | 2.902 | 2.8742 |
While transforming tables (by Python code in a TRANSFORM block) it is possible to execute any sql command. In order to do this the user needs the __connection__ object defined in the Python enviroment.
Example 43.10. Using sql inside TRANSFORM.
# input table:
table 'TAB7':
a b c
'aaa' 10 10
'bbb' 42 20
'ccc' -20 45
'ddd' 35 76
'eee' 88 99
'fff' 31 52
sql:
REPLACE TABLE TAB8 AS
SELECT * FROM TAB7
TRANSFORM:
cur.execute('insert into TAB9 values(?, ?)', (__rowNumber__, b))
GLOBALS:
cur = __connection__.cursor()
cur.execute('drop table if exists TAB9')
cur.execute('create table TAB9(var1 int, var2 int)')
sql a1:
SELECT * FROM TAB8
print a1
sql a2:
SELECT * FROM TAB9
print a2
Output:
a | b | c | +---+-----+----+-- aaa | 10 | 10 | bbb | 42 | 20 | ccc | -20 | 45 | ddd | 35 | 76 | eee | 88 | 99 | fff | 31 | 52 | var1 | var2 | +----+------+-- 0 | 10 | 1 | 42 | 2 | -20 | 3 | 35 | 4 | 88 | 5 | 31 |
Example 43.11. Using sql inside TRANSFORM.
# input table:
table 'TAB7':
a b c
'aaa' 10 10
'bbb' 42 20
'ccc' -20 45
'ddd' 35 76
'eee' 88 99
'fff' 31 52
sql:
REPLACE TABLE TAB8 AS
SELECT * FROM TAB7
TRANSFORM:
if b <= 40:
var1 = a1
else:
var1 = a2
GLOBALS:
cur = __connection__.cursor()
cur.execute('select count(*) from TAB7')
a1 = cur.fetchone()[0]
cur.execute('select count(*) from TAB7 where c > 50')
a2 = cur.fetchone()[0]
DROP a1, a2
sql TAB8_out:
SELECT * FROM TAB8
print TAB8_out
Output:
a | b | c | var1 | +---+-----+----+------+-- aaa | 10 | 10 | 6 | bbb | 42 | 20 | 3 | ccc | -20 | 45 | 6 | ddd | 35 | 76 | 6 | eee | 88 | 99 | 3 | fff | 31 | 52 | 6 |
The TRANSFORM clause cannot be used with:
The TRANSFORM clause can be used with subqueries and views, however such queries will take longer to execute as compared to similar ones without the TRANSFORM part, because a temporary table is created in such cases.