Skip to content

Generate and use Python code

Generate Python code

To generate Python code, we will make use of a JPortal template-generator named the jportal2-generator-vanguard-sqlalchemy template.

Generators, specifically template generators, are a topic all on their own. You can read more about generators (including the difference between built-in and template generators) in < TODO >, but for now, just continue reading below, and follow the instructions.

Type the following command:

generate_jportal.sh
docker run --rm -v ${SCRIPT_DIR}:/local ghcr.io/si-gen/jportal2:latest \
                      --inputdir=/local/sql/si \
                      --builtin-generator PostgresDDL:/local/generated_sources/generated_sql \
                      --template-generator SQLAlchemy:/local/generated_sources/python/jportal \
                      --download-template "SQLAlchemy:https://github.com/SI-Gen/jportal2-generator-vanguard-sqlalchemy/archive/refs/tags/1.8.zip|stripBaseDir"

You will notice that the command line looks very similar to the previous one we ran, to generate Postgres DDL, but it has 2 additional lines.

Line 4 is very similar to line 3. It tells JPortal you want to run the template-generator named SQLAlchemy, and place the generated output files in the directory generated_sources/python/sqlalchemy. The general format of the command is --template-generator <GENERATOR_NAME>:<GENERATED_CODE_DESTINATION.
line 5 is a little different. It tells JPortal where to find and download the SQLAlchemy generator we refer to in line 4, from. For more information about using downloading template generators, and how and why you would want to do that, see < TODO >

Now, navigate to the directory generated_sources/python/jportal/sqlalchemy. You should see a freshly generated file there, named db_ToDoList.py. This is the file that was generated by the SQLAlchemy generator. Let's have a look at the contents of the file:

The generated python code

db_ToDoList.py
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
########################################################################################################################
################## Generated Code. DO NOT CHANGE THIS CODE. Change it in the generator and regenerate ##################
########################################################################################################################

from dataclasses import dataclass, field
from datetime import datetime
from typing import List, Any, Optional
import enum
import sqlalchemy as sa
from sqlalchemy.orm import Session
from sqlalchemy.sql.expression import TextAsFrom

from .common.db_common import DBMixin, Base, DBColumn
from .common import db_types
from .common.processing import process_result_recs, process_result_rec, process_bind_params




@dataclass
class DB_ToDoListInsertReturning:
    # Enum for ListType field
    class ListTypeEnum(enum.Enum):
        Private = 1
        Public = 2

        @classmethod
        def process_result_value_cls(cls, value, dialect):
            return DB_ToDoListInsertReturning.ListTypeEnum(value)


    #Outputs
    ID: int

    @classmethod
    def get_statement(cls
                      , ListName: str
                      , ListType: ListTypeEnum
                      , Description: str
                      , LastUpdated: datetime
                      ) -> TextAsFrom:
        class _ret:
            sequence = "default," #postgres uses default for sequences
            output = ""
            tail = " RETURNING ID"
            #session.bind.dialect.name

        statement = sa.text(
            f"/* PROC ToDoList_App.ToDoList.Insert */"
            f"insert into ToDoList_App.ToDoList ("
            f"  ID,"
            f"  ListName,"
            f"  ListType,"
            f"  Description,"
            f"  LastUpdated"
            f" ) "
            f"{_ret.output}"
            f" values ("
            f"{_ret.sequence}"
            f"  :ListName,"
            f"  :ListType,"
            f"  :Description,"
            f"  :LastUpdated"
            f" )"
            f"{_ret.tail}")

        text_statement = statement.columns(ID=sa.types.Integer,
                                           )
        text_statement = text_statement.bindparams(ListName=ListName,
                                                   ListType=ListType,
                                                   Description=Description,
                                                   LastUpdated=LastUpdated,
                                                   )
        return text_statement

    @classmethod
    def execute(cls, session: Session, ListName: str
                , ListType: ListTypeEnum
                , Description: str
                , LastUpdated: datetime
                ) -> Optional['DB_ToDoListInsertReturning']:
        params = process_bind_params(session, [db_types.NonNullableString,
                                               sa.types.SmallInteger,
                                               db_types.NonNullableString,
                                               sa.types.DateTime,
                                               ], [ListName,
                                                   ListType.value if isinstance(ListType, enum.Enum) else ListType,
                                                   Description,
                                                   LastUpdated,
                                                   ])
        res = session.execute(cls.get_statement(*params))
        rec = res.fetchone()
        if rec:
            res.close()
            return process_result_rec(DB_ToDoListInsertReturning, session, [sa.types.Integer,
                                                                            ], rec)

        return None

@dataclass
class DB_ToDoListUpdate:
    # Enum for ListType field
    class ListTypeEnum(enum.Enum):
        Private = 1
        Public = 2

        @classmethod
        def process_result_value_cls(cls, value, dialect):
            return DB_ToDoListUpdate.ListTypeEnum(value)




    @classmethod
    def get_statement(cls
                      , ListName: str
                      , ListType: ListTypeEnum
                      , Description: str
                      , LastUpdated: datetime
                      , ID: int
                      ) -> TextAsFrom:
        class _ret:
            sequence = "default," #postgres uses default for sequences
            output = ""
            tail = ""
            #session.bind.dialect.name

        statement = sa.text(
            f"update ToDoList_App.ToDoList"
            f" set"
            f"  ListName = :ListName"
            f", ListType = :ListType"
            f", Description = :Description"
            f", LastUpdated = :LastUpdated"
            f" where ID = :ID")

        text_statement = statement.columns()
        text_statement = text_statement.bindparams(ListName=ListName,
                                                   ListType=ListType,
                                                   Description=Description,
                                                   LastUpdated=LastUpdated,
                                                   ID=ID,
                                                   )
        return text_statement

    @classmethod
    def execute(cls, session: Session, ListName: str
                , ListType: ListTypeEnum
                , Description: str
                , LastUpdated: datetime
                , ID: int
                ) -> None:
        params = process_bind_params(session, [db_types.NonNullableString,
                                               sa.types.SmallInteger,
                                               db_types.NonNullableString,
                                               sa.types.DateTime,
                                               sa.types.Integer,
                                               ], [ListName,
                                                   ListType.value if isinstance(ListType, enum.Enum) else ListType,
                                                   Description,
                                                   LastUpdated,
                                                   ID,
                                                   ])
        res = session.execute(cls.get_statement(*params))
        res.close()

@dataclass
class DB_ToDoListSelectOne:
    # Enum for ListType field
    class ListTypeEnum(enum.Enum):
        Private = 1
        Public = 2

        @classmethod
        def process_result_value_cls(cls, value, dialect):
            return DB_ToDoListSelectOne.ListTypeEnum(value)


    #Outputs
    ListName: str
    ListType: ListTypeEnum
    Description: str
    LastUpdated: datetime

    @classmethod
    def get_statement(cls
                      , ID: int
                      ) -> TextAsFrom:
        class _ret:
            sequence = "default," #postgres uses default for sequences
            output = " OUTPUT (ListName,ListType,Description,LastUpdated)"
            tail = " RETURNING ListName ListType Description LastUpdated"
            #session.bind.dialect.name

        statement = sa.text(
            f"/* PROC ToDoList_App.ToDoList.SelectOne */"
            f"select"
            f"  ListName"
            f", ListType"
            f", Description"
            f", LastUpdated"
            f" from ToDoList_App.ToDoList"
            f" where ID = :ID")

        text_statement = statement.columns(ListName=db_types.NonNullableString,
                                           ListType=sa.types.SmallInteger,
                                           Description=db_types.NonNullableString,
                                           LastUpdated=sa.types.DateTime,
                                           )
        text_statement = text_statement.bindparams(ID=ID,
                                                   )
        return text_statement

    @classmethod
    def execute(cls, session: Session, ID: int
                ) -> Optional['DB_ToDoListSelectOne']:
        params = process_bind_params(session, [sa.types.Integer,
                                               ], [ID,
                                                   ])
        res = session.execute(cls.get_statement(*params))
        rec = res.fetchone()
        if rec:
            res.close()
            return process_result_rec(DB_ToDoListSelectOne, session, [db_types.NonNullableString,
                                                                      DB_ToDoListSelectOne.ListTypeEnum,
                                                                      db_types.NonNullableString,
                                                                      sa.types.DateTime,
                                                                      ], rec)

        return None

@dataclass
class DB_ToDoListDeleteOne:


    @classmethod
    def get_statement(cls
                      , ID: int
                      ) -> TextAsFrom:
        class _ret:
            sequence = "default," #postgres uses default for sequences
            output = ""
            tail = ""
            #session.bind.dialect.name

        statement = sa.text(
            f"/* PROC ToDoList_App.ToDoList.DeleteOne */"
            f"delete from ToDoList_App.ToDoList"
            f" where ID = :ID")

        text_statement = statement.columns()
        text_statement = text_statement.bindparams(ID=ID,
                                                   )
        return text_statement

    @classmethod
    def execute(cls, session: Session, ID: int
                ) -> None:
        params = process_bind_params(session, [sa.types.Integer,
                                               ], [ID,
                                                   ])
        res = session.execute(cls.get_statement(*params))
        res.close()

@dataclass
class DB_ToDoListSelectAll:
    # Enum for ListType field
    class ListTypeEnum(enum.Enum):
        Private = 1
        Public = 2

        @classmethod
        def process_result_value_cls(cls, value, dialect):
            return DB_ToDoListSelectAll.ListTypeEnum(value)


    #Outputs
    ID: int
    ListName: str
    ListType: ListTypeEnum
    Description: str
    LastUpdated: datetime

    @classmethod
    def get_statement(cls
                      ) -> TextAsFrom:
        class _ret:
            sequence = "default," #postgres uses default for sequences
            output = " OUTPUT (ID,ListName,ListType,Description,LastUpdated)"
            tail = " RETURNING ID ListName ListType Description LastUpdated"
            #session.bind.dialect.name

        statement = sa.text(
            f"/* PROC ToDoList_App.ToDoList.SelectAll */"
            f"select"
            f"  ID"
            f", ListName"
            f", ListType"
            f", Description"
            f", LastUpdated"
            f" from ToDoList_App.ToDoList")

        text_statement = statement.columns(ID=sa.types.Integer,
                                           ListName=db_types.NonNullableString,
                                           ListType=sa.types.SmallInteger,
                                           Description=db_types.NonNullableString,
                                           LastUpdated=sa.types.DateTime,
                                           )
        return text_statement

    @classmethod
    def execute(cls, session: Session) -> List['DB_ToDoListSelectAll']:
        res = session.execute(cls.get_statement())
        recs = res.fetchall()
        return process_result_recs(DB_ToDoListSelectAll, session, [sa.types.Integer,
                                                                   db_types.NonNullableString,
                                                                   DB_ToDoListSelectAll.ListTypeEnum,
                                                                   db_types.NonNullableString,
                                                                   sa.types.DateTime,
                                                                   ], recs)

@dataclass
class DB_ToDoListStaticData:


    @classmethod
    def get_statement(cls
                      ) -> TextAsFrom:
        class _ret:
            sequence = "default," #postgres uses default for sequences
            output = ""
            tail = ""
            #session.bind.dialect.name

        statement = sa.text(
            f"INSERT INTO ToDoList_App.ToDoList(ListName,ListType,Description,LastUpdated) VALUES ('Takeon Test List 1', 1, 'Take on test list description', CURRENT_DATE );")

        text_statement = statement.columns()
        return text_statement

    @classmethod
    def execute(cls, session: Session) -> None:
        res = session.execute(cls.get_statement())
        res.close()
Line 1-16 basically just sets up a bunch of imports required. Nothing fancy here.
Line 20-75 is a bit more interesting. Let's have a look.

Insert Returning

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
@dataclass
class DB_ToDoListInsertReturning:
    # Enum for ListType field
    class ListTypeEnum(enum.Enum):
        Private = 1
        Public = 2

        @classmethod
        def process_result_value_cls(cls, value, dialect):
            return DB_ToDoListInsertReturning.ListTypeEnum(value)


    #Outputs
    ID: int

    @classmethod
    def get_statement(cls
                      , ListName: str
                      , ListType: ListTypeEnum
                      , Description: str
                      , LastUpdated: datetime
                      ) -> TextAsFrom:
        class _ret:
            sequence = "default," #postgres uses default for sequences
            output = ""
            tail = " RETURNING ID"
            #session.bind.dialect.name

        statement = sa.text(
            f"/* PROC ToDoList_App.ToDoList.Insert */"
            f"insert into ToDoList_App.ToDoList ("
            f"  ID,"
            f"  ListName,"
            f"  ListType,"
            f"  Description,"
            f"  LastUpdated"
            f" ) "
            f"{_ret.output}"
            f" values ("
            f"{_ret.sequence}"
            f"  :ListName,"
            f"  :ListType,"
            f"  :Description,"
            f"  :LastUpdated"
            f" )"
            f"{_ret.tail}")

        text_statement = statement.columns(ID=sa.types.Integer,
                                           )
        text_statement = text_statement.bindparams(ListName=ListName,
                                                   ListType=ListType,
                                                   Description=Description,
                                                   LastUpdated=LastUpdated,
                                                   )
        return text_statement

    @classmethod
    def execute(cls, session: Session, ListName: str
                , ListType: ListTypeEnum
                , Description: str
                , LastUpdated: datetime
                ) -> Optional['DB_ToDoListInsertReturning']:
        params = process_bind_params(session, [db_types.NonNullableString,
                                               sa.types.SmallInteger,
                                               db_types.NonNullableString,
                                               sa.types.DateTime,
                                               ], [ListName,
                                                   ListType.value if isinstance(ListType, enum.Enum) else ListType,
                                                   Description,
                                                   LastUpdated,
                                                   ])
        res = session.execute(cls.get_statement(*params))
        rec = res.fetchone()
        if rec:
            res.close()
            return process_result_rec(DB_ToDoListInsertReturning, session, [sa.types.Integer,
                                                                            ], rec)

        return None

Now this is a lot more interesting. We are declaring a class called DB_ToDoListInsertReturning, but it has several member fields and methods.

The first thing we notice, is that on line 23-29, we generate an Enum called ListTypeEnum. Remember how when we created the SI file, we created the field ListType as a SHORT, but we specified that we wanted an enum with two value, "Public" and "Private"? The SQLAlchemy generator picked that up, and generated an enum for us, to use in our code. For more information about enums, see < TODO >.

Next, on line 32-33 we specify that this class has an OUTPUT member ID. In JPortal2, database operations can have INPUT and/or OUTPUT fields. Input fields are obviously fields you pass into the class, before it interacts with the database. Output fields are fields we want to get back from the database.

In this case, remember we specified that we wanted JPortal to create an Insert function for us, specifically an Insert Returning, which would return the Primary Key after inserting? In this table, ID is the primary key, so the Insert Returning must return the ID field for us.

Let's move on the rest of the file.

Line 35-74 is a method called get_statement. If you look over the code, you will see it basically creates an inline SQL statement that allows you to insert an entire ToDoList record. Most of it is probably very straightforward, but you may be confused by the lines that contain "_ret.". Don't worry about it too much. For now, just accept that this is required because different databases like Postgres, SQLServer, Oracle, DB/2 etc. all have slightly varying syntaxes, and this allows us to cater for differences at runtime.

Finally, we get to line 97-98. This is a method called execute(), which does exactly what it says, it executes the query defined above in get_statement, and reads back the inserted ID field that was returned.

We aren't going to go through the the remainder of the generated code, because it is all essentially a repetition of lines 20-98, albeit for the other built-in queries we wanted, i.e. Update, SelectOne, DeleteOne and finally SelectAll.

Interact with our database using Python

Now that we have our Data Access Layer (DAL) defined, let's write a quick python program to use is.

Create a file called python/jportal_example.py. Your folder structure should now look like this: 

jportal2-demo
└───.vscode
    └── settings.json
└───sql
    └───si
        └── todolist.si
└───python
    └── jportal_example.py

SelectAll

Paste the following code into the file:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
from datetime import datetime
import sys
from jportal import sqlalchemy as jportal
import sqlalchemy as sa
from sqlalchemy.orm import Session

engine = sa.create_engine("postgresql+psycopg2://postgres:magic_password@localhost/postgres")

session = Session(engine)

### ToDoList ###
#Select All
def select_all_from_todo_list(session):
    recs = jportal.DB_ToDoListSelectAll.execute(session)
    for rec in recs:
        print(f"ID: {rec.ID}")
        print(f"ListName: {rec.ListName}")
        print(f"ListType: {rec.ListType}")
        print(f"Description: {rec.Description}")
        print("***")


select_all_from_todo_list(session)
session.rollback() #For the demo, normally you'd call session.commit() here

Let's look at it piece by piece:
Line 1-5 simply imports the required modules.
We import our generated code as jportal.
Then we import sqlalchemy. SQLAlchemy is a popular ORM for Python. We actually don't like ORM's, we feel they add unnecessary complications to development. We feel that plain old SQL is far easier to read/write/debug and understand.
That said, SQLAlchmey does some useful things, like connection pooling. So as a result, we use the SQLAlchemy session management as a base library, and build our generated classes on top of it.

Line 7 simply opens a SQLAlchemy connection to our postgres database. The username and password are created when we start the docker container inside start_postgres.sql.

Line 11-20 we create a simple function that calls our generated SelectAll function. SelectAll simply returns all the records in a table. In reality, you almost never want to return all the records in a table unless it is a small lookup table, but for our demo, SelectAll is useful.

On line 23 we call the function we defined up top, and on line 24 we simply rollback the SQLAlchemy connection to avoid a warning when we exit the program.

Save the file. Assuming you are using our dev container repo, you can either press F5 to execute the program, or use the normal VSCode run mechanism. If you are not using our dev container, you can run the code using python jportal_example.py, but bear in mind you will need to add the generated_code/python/jportal folder to the PYTHONPATH, and also install the requirements defined in python/requirements.txt. We are not going to talk through that in this tutorial.

Once you run the example program, you should see the following output: 

ID: 1
ListName: Takeon Test List 1
ListType: ListTypeEnum.Private
Description: Take on test list description
***

You might recognise this as the data we added in our SI file in the SQLDATA section, and which was inserted when we ran the ExampleDatabase.sql DDL script.

How easy was that?

Insert Returning

Let's test the rest of the generated code. Change jportal_example.py to look like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
from datetime import datetime
import sys
print("PYTHONPATH=",sys.path)
from jportal import sqlalchemy as jportal
import sqlalchemy as sa
from sqlalchemy.orm import Session

engine = sa.create_engine("postgresql+psycopg2://postgres:magic_password@localhost/postgres")



session = Session(engine)

### ToDoList ###
#Select All
def select_all_from_todo_list(session):
    recs = jportal.DB_ToDoListSelectAll.execute(session)
    for rec in recs:
        print(f"ID: {rec.ID}")
        print(f"ListName: {rec.ListName}")
        print(f"ListType: {rec.ListType}")
        print(f"Description: {rec.Description}")
        print("***")

### ToDoList ###
#Insert Returning
def insert_returning_todo_list(session):
    #Insert
    tdl_ret = jportal.DB_ToDoListInsertReturning.execute(session, 
                                                        "New List", 
                                                        jportal.DB_ToDoListInsertReturning.ListTypeEnum.Private,
                                                        "Some Description",
                                                        datetime.now())

    return tdl_ret


select_all_from_todo_list(session)

tdl_ret = insert_returning_todo_list(session)
print(f"Added record {tdl_ret.ID} into ToDoList")

select_all_from_todo_list(session)

session.rollback() #For the demo, normally you'd call session.commit() here

On line 29 we insert a new record into the ToDoList table:
- We name the list "New List"
- We set the ListType as "Private". Notice how we can use the generated enum to set this!
- We add a simple description field.
- Finally we set the TMStamp field to the current time.

Once we have done the insert, we call our select_all function again, to see the record we added.

Run the program again. Now the output should look something like this: 

ID: 1
ListName: Takeon Test List 1
ListType: ListTypeEnum.Private
Description: Take on test list description
***
Added record 2 into ToDoList
ID: 1
ListName: Takeon Test List 1
ListType: ListTypeEnum.Private
Description: Take on test list description
***
ID: 2
ListName: New List
ListType: ListTypeEnum.Private
Description: Some Description
***

SelectOne and Update

Finally, change the jportal_example.py file to look like this: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
from datetime import datetime
import sys
print("PYTHONPATH=",sys.path)
from jportal import sqlalchemy as jportal
import sqlalchemy as sa
from sqlalchemy.orm import Session

engine = sa.create_engine("postgresql+psycopg2://postgres:magic_password@localhost/postgres")



session = Session(engine)

### ToDoList ###
#Select All
def select_all_from_todo_list(session):
    recs = jportal.DB_ToDoListSelectAll.execute(session)
    for rec in recs:
        print(f"ID: {rec.ID}")
        print(f"ListName: {rec.ListName}")
        print(f"ListType: {rec.ListType}")
        print(f"Description: {rec.Description}")
        print("***")

### ToDoList ###
#Insert Returning
def insert_returning_todo_list(session):
    #Insert
    tdl_ret = jportal.DB_ToDoListInsertReturning.execute(session, 
                                                        "New List", 
                                                        jportal.DB_ToDoListInsertReturning.ListTypeEnum.Private,
                                                        "Some Description",
                                                        datetime.now())

    return tdl_ret

### ToDoList ###
#Select One
def select_one_from_todo_list(session):
    rec = jportal.DB_ToDoListSelectOne.execute(session,1)
    print(f"ListName: {rec.ListName}")
    print(f"ListType: {rec.ListType}")
    print(f"Description: {rec.Description}")
    print("***")

### ToDoList ###
#Select All
def update_todo_list(session):
    recs = jportal.DB_ToDoListUpdate.execute(session,
                                                "Updated ListName",
                                                jportal.DB_ToDoListUpdate.ListTypeEnum.Private,
                                                "Updated description",
                                                datetime.now(),
                                                1)

select_all_from_todo_list(session)

tdl_ret = insert_returning_todo_list(session)
print(f"Added record {tdl_ret.ID} into ToDoList")

select_one_from_todo_list(session)
update_todo_list(session)
select_all_from_todo_list(session)




session.rollback() #For the demo, normally you'd call session.commit() here

Run it again. The output should look something like this now (the ID's might be slightly different depending on how many times you ran it, because of the behaviour of the SEQUENCE datatype):

ListName: Takeon Test List 1
ListType: ListTypeEnum.Private
Description: Take on test list description
***
ID: 5
ListName: New List
ListType: ListTypeEnum.Private
Description: Some Description
***
ID: 1
ListName: Updated ListName
ListType: ListTypeEnum.Private
Description: Updated description
***

Summary

At this point, take a step back and consider the power of JPortal2. If you look at our initial SI file, you will see that in 17 lines, we defined a database, a schema, a table with 5 fields, and a complex insert statement. Using these 17 lines we generated nearly 20 lines of Postgres Specific DDL, and over a 100 lines of Python code. That is a huge amount of code you didn't need to write or think about.