Data transformations are performed by the trans procedure, which creates a new table in a database with the transformed values from the source table or tables. It is possible to create tables with the same columns as the input table, as well as with more or fewer columns. More than one input table can be used to produce the output table. During transformations a variety of mathematical procedures can be used.
The basic transformation which copies a table has the following syntax:
trans outtable <- intable: transformations
intable and outtable are the names of tables in a database (it may also be a Gython string variable). That string may be a table name or may have the form databasealias.tablename. In first case the default database alias is used.
The transformations part must consist of valid Gython assignment instructions, if statements, and function calls. Transformations are calculated once for each row in the input table. After calculating each input row a new output row is created in the output table (sometimes it may be skipped, which will be described later). Transformations may use all variables or functions defined in the script. They are indicated by the escape character '$'. Special variables are also available: they have the same names as the columns in the input table. Their values are equal to the column values from the current row of the input table. The None value in the script corresponds to the NULL value in the database.
Example 16.17. The trans procedure, example 1:
# input table:
table 'transactions':
id type value currency
'c20' 'in' 400 'USD'
'c10' 'out' 800 'USD'
'c50' 'out' 500 'USD'
'e80' 'in' 80 'USD'
_id = 5
print "id type value currency"
print "______________________"
trans '_out_' <- 'transactions':
id = id + "%s" %$_id
value = value * 0.75
currency = 'EU'
val_txt = $str(value) # converting number to string
print id,type,$repr(value),currency, $repr(val_txt) # the repr() function shows
# the representation of a variable
Output:
id type value currency
______________________
c205 in 300.0 EU '300.0'
c105 out 600.0 EU '600.0'
c505 out 375.0 EU '375.0'
e805 in 60.0 EU '60.0'
All columns from the source table appear in the output table. Furthermore, the variables defined in the trans procedure body also appear in the output table. Variables that were not defined before the execution of transformations (i.e. local variables) are always reset to None (null value) before transforming the next row. Variables that were defined earlier (i.e. global variables) change only when their values are assigned explicitly or by reading from the input table column with a matching name.
Example 16.18. The trans procedure, example 2:
# input table :
table 'transactions':
id value
1 400
5 800
7 100
2 800
vat = 0.22
print "id value tax income"
print "___________________"
trans '_out_' <- 'transactions':
income = value
if value>300:
tax = value * $vat
income = value - tax
print id,value,tax,income
Output:
id value tax income
___________________
1 400 88.0 312.0
5 800 176.0 624.0
7 100 None 100
2 800 176.0 624.0
All newly created variables are visible also after transforming all of the data. This means that two exactly same data blocks may give different results, because variables created in the first table procedure are global in the second block (variables may also be removed using the standard python instruction
del vars()['variable_name'])
There are special variables that can be used in transformations:
__rowNumber__ - stores the number of processed rows from the input table (__rowNumber__ is 0 for the first row, 1 for the second ...)
__skipRow__ - prevents the current row from being written to the output table: if it is set to a value other than 0 the current row will not be skiped
__exit__ - if set to a value other than 0 terminates the table procedure without saving the current row
__vars__ - a list which contains all the defined variables,
__inputNames__ - a list which contains all variables corresponding to the columns from the input table.
Example 16.19. The trans procedure, example 3:
# input table :
table '_in_':
id
1
5
7
2
6
8
3
trans '_out_' <- '_in_':
if __rowNumber__ % 2 == 0:
__skipRow__ = 1
if __rowNumber__ == 4:
__exit__ = 1
# print transformation result
print "id"
print "_"
trans None <- '_out_':
print id
Output:
id
_
5
2
Example 16.20. Time and date usage inside the trans procedure: example 1
table 'clients':
format BeginDate DATE 'yyyy/MM/dd'
format EndDate DATE 'yyyy-MM-dd'
Id Name BeginDate EndDate
'1012' 'Bush' '2001/12/07' '2006-01-31'
'1013' 'Kennedy' '2005/07/11' None
'1015' 'Washington' '2004/04/21' None
'1019' 'Lincoln' '2002/01/29' '2008-07-05'
days = {0: 'Monday', 1: 'Tuesday', 2: 'Wednesday', 3: 'Thursday', 4: 'Friday', 5: 'Saturday', 6: 'Sunday'}
trans 'clients2' <- 'clients':
weekDay = $days[BeginDate.weekday()]
howLongAgo = (($date(2009,01,01).today() - BeginDate).days)/365
if EndDate != None:
howLong = ((EndDate - BeginDate).days)/365
else:
howLong = howLongAgo
whichYearNextYear = $date(2009,12,01) + $timedelta(weeks = 52)
sql a:
select * from clients2
print a
Output:
Id | Name | BeginDate | EndDate | howLongAgo | whichYearNextYear | weekDay | howLong |
+----+------------+------------+------------+------------+-------------------+-----------+---------+--
1012 | Bush | 2001-12-07 | 2006-01-31 | 9 | 2010-11-30 | Friday | 4 |
1013 | Kennedy | 2005-07-11 | None | 6 | 2010-11-30 | Monday | 6 |
1015 | Washington | 2004-04-21 | None | 7 | 2010-11-30 | Wednesday | 7 |
1019 | Lincoln | 2002-01-29 | 2008-07-05 | 9 | 2010-11-30 | Tuesday | 6 |
Example 16.21. Time and date usage inside the trans procedure: example 2
table 'schedule':
format name VARCHAR(32)
format date DATE 'yyyy-MMM-dd' 'en'
format startWorkAt TIME 'HH:mm:ss'
format brakeStart TIME 'HH:mm:ss'
format brakeEnd TIME 'HH:mm:ss'
format endWorkAt TIME 'HH:mm:ss'
name date startWorkAt brakeStart brakeEnd endWorkAt
'Bush' '2006-oct-03' '07:12:60' '12:37:00' '13:12:00' '18:30:16'
'Kennedy' '2006-oct-03' '08:15:12' '12:12:00' '13:00:19' '17:13:30'
'Washington' '2006-oct-03' '09:12:00' '12:30:00' '13:45:00' '17:10:00'
'Lincoln' '2006-oct-03' '07:10:00' '11:13:00' '11:30:00' '19:43:00'
baseWorkTime = 8
strFormat = "%-20s%10s%15s%10s%10s"
print strFormat % ("name","date","dailyWage","bonus","salary")
trans None <- 'schedule':
brakeTime = (brakeEnd-brakeStart)
timeSpentAtWork = (endWorkAt - startWorkAt) - brakeTime
basetime = $baseWorkTime
bonus = 0
overtime = 0
dailyWage = 0
hoursSpentAtWork = timeSpentAtWork.seconds/3600
if hoursSpentAtWork > 8:
overtime = (timeSpentAtWork - $timedelta(hours=basetime)).seconds/3600
dailyWage = basetime * 10
bonus = overtime * 15
else:
dailyWage = hoursSpentAtWork * 10
salary = dailyWage + bonus
print $strFormat % (name,date,dailyWage,bonus,salary)
Output:
name date dailyWage bonus salary
Bush 2006-10-03 80 30 110
Kennedy 2006-10-03 80 0 80
Washington 2006-10-03 60 0 60
Lincoln 2006-10-03 80 60 140
In the examples above all rows from the input table were read. For efficiency it is possible to read only the selected rows from the input table. This can be accomplished with the where keyword.
Syntax:
trans outtable <- intable: where condition transformations
condition must return a boolean value. All variables used the in the condition must be column names. Any valid sql expression is allowed.
Example 16.22. The where keyword: example 1
# input table :
table '_in_':
id
1
5
7
2
1
7
2
3
5
6
2
strFormat = "%5s%5s"
print strFormat % ("rowNo","id")
trans '_out_' <- '_in_':
where id < 5
rowNo = __rowNumber__
print $strFormat % (rowNo,id)
Output:
rowNo id
0 1
1 2
2 1
3 2
4 3
5 2
Example 16.23. The where keyword: example 2
# input table :
table '_in_':
id
5
7
2
1
7
2
3
5
6
2
trans '_out_' <- '_in_':
where id <>- (select sum(id)/8 from _in_)
a = 100
trans None <- '_out_':
print id, a
Output:
5 100
7 100
2 100
1 100
7 100
2 100
3 100
5 100
6 100
2 100
There are also other keywords that may appear between trans and the transformations part (see below), but where must be used after all other modifiers described below.
It is not necessary to read all the columns from the input table. Some columns may be skipped using the drop in and keep in keywords.
Syntax:
trans outtable <- intable: keep in variable , variable ... keep in variable , variable ... ... drop in variable , variable ... drop in variable , variable ... ... transformations
If a variable is specified after one of the drop in statements, the column with the same name will not be read from the input table. If at least one keep in statement exists, only the columns specified after keep in will be read from the input table (unless they are also specified after drop in).
Example 16.24. Using the drop in keyword
# input table :
table '_in_':
a b c d e f
1 2 3 4 5 6
trans '_out_' <- '_in_':
drop in a, b
drop in c
drop in e, f
pass
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j]
Output:
d
4.0
Example 16.25. Using the keep in keyword
# input table :
table '_in_':
a b c d e f
1 2 3 4 5 6
trans '_out_' <- '_in_':
keep in a, d
keep in b
pass
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
a b d
1.0 2.0 4.0
Note that the pass statement was used, because there was no need to perform any data transformations.
It is also possible to specify which columns should not be written to the output table even though a new variable is created by the table procedure. The keywords keep out and drop out are used for that purpose. They work in the same way as keep in and drop in, but operate on the output table.
Example 16.26. Using the keep out keyword
# input table :
table 'workers':
id surname income
1 'Smith' 300
2 'Johnson' 360
3 'Wilson' 280
trans '_out_' <- 'workers':
keep out surname, bonus
bonus = income * 0.12
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
surname bonus
Smith 36.0
Johnson 43.199999999999996
Wilson 33.6
The keywords keep in, drop in, keep out, drop out may be used together.
The data type of a columns created in the output table is determined automatically based on the first value that is written to the table. If subsequent values have different type they will be converted (if possible) to the determined type. The user may also specify explicitly the data type of the column using the format keyword.
Syntax:
trans outtable <- intable: format column_name column_format , column_name column_format ... format column_name column_format , column_name column_format ... ... transformations
where column_format is one of:
TINYINT
SMALLINT
INTEGER
BIGINT
REAL
FLOAT
DOUBLE
LONGVARCHAR
DATE
TIME
TIMESTAMP
CHAR(size)
VARCHAR(size)
NUMERIC(precision, decimal_digits)
DECIMAL(precision, decimal_digits)
Some databases may not support all of the above types.
Example 16.27. Using the trans procedure with the format keyowrd
# input table :
table 'presidents':
format id TINYINT, name VARCHAR(32),surname LONGVARCHAR, description VARCHAR(64)
id name surname description
32 'Jimmy' 'Carter' 'Jr.--was born October 1, 1924, in Plains...'
14 'George' 'Washington' 'Born in 1732 into a Virginia planter...'
30 'John' 'Kennedy' 'he was born in Brookline, Massachusetts, on...'
trans '_out_' <- 'presidents':
format id BIGINT, initials VARCHAR(3),description LONGVARCHAR
drop out name
drop out surname
initials = name[0] + "." + surname[0]
id = id * 1000
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
id description initials
32000.0 Jr.--was born October 1, 1924, in Plains... J.C
14000.0 Born in 1732 into a Virginia planter... G.W
30000.0 he was born in Brookline, Massachusetts, on... J.K
When transforming tables it is important to preserve existing indexes or create new ones. Indexes are created in the same way as in the table procedure. In order to preserve the existing indices the auto index instruction can be used.
Example 16.28. Automatic indexing with the trans procedure
list = [['2005-04-12', 12, 'aaa'], ['2007-08-16', None, 'bbb'], [None, 10, None]]
table 'TAB1' <- list:
format a DATE 'yyyy-MM-dd'
format b INTEGER
format c VARCHAR(10)
index on c "c_index"
a b c
trans 'TAB2' <- 'TAB1':
auto index #existing indexes in TAB1
index on d "d_index" #new index
d = 1
print d,a
Output
1 2005-04-12
1 2007-08-16
1 None
Some databases (including GDBase) require indexes to have unique names (there can not be two indexes with the same name even if they were created on\ different tables). For this reason when transorming tables and using auto index, the indexes in the output table will get the prefix inherited from the output table name, e.g. the following code will create three indexes: idx, t2_idx, t3_idx.
table 't':
index on a "idx"
a
1
trans 't2' <- 't':
auto index
pass
trans 't3' <- 't2':
auto index
pass
The trans procedure supports flow control with statements like if, for, while. A loop provides the means to carry out a series of similar instructions, but only within a single line.
Example 16.29. Loops and if statements with the trans procedure, example 1
table 'TAB1':
a b c
1 2 3
None 20 30
5 7 None
col = tableColumns('TAB1')
trans None <- 'TAB1':
for i in ['a', 'b', 'c']:
if i == 'a':
print __rowNumber__, i
else:
print i
Output
0 a
b
c
1 a
b
c
2 a
b
c
Example 16.30. Loops and if statements with the trans procedure, example 2
table 'TAB1':
a b c
1 2 3
None 20 30
5 7 None
col = tableColumns('TAB1')
trans 'TAB2' <- 'TAB1':
for i in $col:
if __vars__[i] != None:
__vars__[i] = __vars__[i] * 10
trans None <- 'TAB2':
print a,b,c
Output:
10 20 30
None 200 300
50 70 None
It is possible to create the output table by combining rows from a number of input tables.
Syntax:
trans outtable <- intable1, intable2, ..., intableN: transformations
Columns with the same names in input tables will be transformed into the same column in the output table. If during row transformation a variable was not set (because it does not exist in the current table) its value is set to None (unless it is a global variable). The same table may be specified more than once in the list of input tables: in that case the transformations are executed as many times for that table as it is specified. Tables are always transformed in the order they are listed.
Example 16.31. Trans procedure (appending tables):
# input table 1 :
table 'workers1':
id name
1 'Nick'
2 'John'
3 'Jack'
# input table 2 :
table 'workers2':
id income
4 3000
5 7000
6 2000
trans '_out_' <- 'workers1', 'workers2':
rowNo = __rowNumber__
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
id name rowNo income 1.0 Nick 0.0 NULL 2.0 John 1.0 NULL 3.0 Jack 2.0 NULL 4.0 NULL 3.0 3000.0 5.0 NULL 4.0 7000.0 6.0 NULL 5.0 2000.0
It may be desirable to use a variable name different then the column name. e.g. in the last example one might want to create an output table with the first column named 'a' and the second column with a different name, but containing values from columns 'b' and 'c'. The easiest way to do this is by using the keyword rename.
Syntax:
trans outtable <- intable: rename oldVarName1 newVarName1 , oldVarName2 newVarName2 ... # may be used if there is only one input table rename table tableName1 oldVarName1 newvarName1 , table tableName2 oldVarName2 newVarName2, ... # must be used if there is more than one table ... transformations
table_name must be the same string that was specified in the input table list (it is not allowed to specify one name as a string and the other one as string variable equal to that string). After renaming all commands except where (e.g. keep, format ..) must use the new name. The original name can be used only by the where keyword.
Example 16.32. Using the rename keyword with the trans procedure
# input table 1 :
table 'workers1':
id surname
1101 'Smith'
1202 'Johnson'
1209 'Bush'
# input table 2 :
table 'workers2':
id second_name
2009 'Wilson'
2100 'Carter'
2900 'Washington'
trans '_out_' <- 'workers1', 'workers2':
rename table 'workers1' id email
rename table 'workers2' second_name surname
rename table 'workers2' id email
email = surname + $str(email) + "@mycompany.com"
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
email surname
Smith1101@mycompany.com Smith
Johnson1202@mycompany.com Johnson
Bush1209@mycompany.com Bush
Wilson2009@mycompany.com Wilson
Carter2100@mycompany.com Carter
Washington2900@mycompany.com Washington
With the join keyword it is possible to create an output table which is a combination of rows from many different tables. To perform joining a key according to which the tables are joined has to be specified.
Syntax:
trans outtable <- intable1, intable2 , intableN ...: join by key force in table_name , table_name ... force in table_name , table_name ... ... transformations
table_name must be the same string which was specified in the input table list. If the force in keyword is omitted then the output table will contain only rows that have the same key value in all input tables. If force in is present than every row with unmatched key value will appear also in the output table. In that case all columns from other tables will be NULL. If more than one input tables have a column with an identical name (except key) than they must be renamed. The join by keyword must be the last keyword before the transformations. The where keyword cannot be used together with join by.
Example 16.33. Joining tables with the trans procedure
# input table 1 :
table 'workers':
id name
10 'Carter'
20 'Bush'
30 'Washington'
40 'Lincoln'
# input table 2 :
table 'income':
id salary bonus
10 2000 100
20 3000 300
30 1900 1000
# input table 3 :
table 'group':
id name
10 'Admin'
20 'Marketing'
40 'IT'
trans '_out_' <- 'workers', 'income', 'group':
rename table 'group' name groupName
join by id
force in 'workers'
pass
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
id name salary bonus groupName
10.0 Carter 2000.0 100.0 Admin
20.0 Bush 3000.0 300.0 Marketing
30.0 Washington NULL NULL NULL
40.0 Lincoln NULL NULL NULL
MSSQL databases may not support joining tables when the key is defined as LONGVARCHAR. In that case CHAR or VARCHAR should be used.
None can be specified as the output table. In that case all transformations will be computed, but no output table will be created. The only result of trans procedure execution will be the resulting values of all variables. Moreover, if all columns are removed from the output table using the drop out keyword, no table will be created. If all columns are removed from the input table(s) using the drop in keyword, then the transformations none the less will be executed as many times as many there are rows in the input table(s).
If in every row a variable is set to None the table will not be created unless the format keyword is used to describe that column. This concerns both the table and trans procedures.
Gython keywords may not be used as column names. These include: and, assert, break, class, continue, def, del, elif, else, except, exec, finally, for, from, global, if, import, in, is, lambda, not, or, pass, print, raise, return, try, while, yield, format, drop, keep, None, out, where, rename, join, by, force, rename, table
The order of keywords in declaration part (before transformations) is very important. The correct order is: where, rename, format, index, join, drop/keep. Of course not every word must be used.