Python 3 Iterating trough recordset in a list

I am using Python 3.3.

I have a result set that I get from an SQL database that looks like this:

db,            schema,            tbl,      column,            type,            size    ord_pos            idx_ord
db1            dbo                  tbl1      id                  int                              1              1
db1            dbo                  tbl1      txt                  varchar            30       2            
db1            dbo                  tbl1      dte                  datetime                  3                    2
db1            dbo                  tbl2      id                  int                              1
db1            dbo                  tbl2      txt                  varchar            30      2              1

I want to iterate through the list I get from the database and build a create statement for each table. The problem I have is that when the list ends I can't wrap up the sql statement I built.

Is there an easy way of determining when the list with the result set ends? I need to add the indexes after table ends and when the list ends. I don't want to repeat the wrapping part after the loop if possible.

I am asking not becasue i couldn't find a solution but because my solution is uggly and I am thinking there are better ways to do this in Python.

Here is a simplified code i use:

... (i am querying the database and the result gets into a list called rows)
for db,sch,tbl,col,typ,sze,ord,idx in rows:
	if curr_tbl != tbl:
		print("CREATE TABLE "+tbl+" ("+col+" "+typ+"("+size+")")

	#when table ends add the index on col1, col2(if composite ther eare 2 columns in index)
		print("CREATE INDEX ON "+col)

	if ord > 1:
		print("ALTER "+tbl+" ADD "+col+" "+typ+"("+size+")"

Open in new window

LVL 27
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The problem I have is that when the list ends I can't wrap up the sql statement I built.

You have two tables listed in our example: tbl1 (three information lines related to the three columns of the table), and tbl2 (two columns). But you need to collect the information about all of the columns first to be able to create table (and index,...). Because of that you have chosen the approach to do the actions only when the table identifier changes. However, records for the last table are not processed because the loop ends. Do I understand it well?

Firstly, you have to collect the information for one table in several loops. It is not shown in your code. Your code creates the tbl1 three times -- with a single column each time (not speaking about problems with creation of the existing table).

I would reformulate the thinking to the steps like this: 1) collect all the info for one table, 2) only after collecting all the needed information, the creation and the related actions can be executed.

The problem of detection of "when the information for one table is available" remains and the technique may also be the same (to compare the table identifier).

It may not be possible to avoid the call of the same functionality on more places -- i.e. once inside the loop, and once just after the loop. To avoid duplication of the code, you can simply define your function for create+index+... and pass it the collected information. Sure, there will be two calls of the function, but the code is written on one place.
ZberteocAuthor Commented:
I understand what you are saying and I completely agree. I did all those things initially and my code was working but became a bit bloated. I hoped that somebody did that before in a simpler way.

Another thing that I tried was to add an extra "row" to the list with "#EOF#" values for db, schema, table, etc and I check for that to know that I am at the end.

Also I want to do all these things with only one iteration through the set/list, including the index creation, in order to make the code faster. I didn't provide all the details and the sample code is incomplete but I only wanted to give a clue about what I am trying.

It is weird that there is no "natural" way to detect the end of a list in a loop the same you can in a SQL cursor for instance and you actually have to implement some helping steps.

Anyway, thanks for your input which is still useful to me. I will wait and see if more will come up.
ZberteocAuthor Commented:
By the way, I forgot to mention one thing, I have a fair experience in programming and especially in SQL code but there are my first steps in Python and that is why I am seeking for second opinions.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

I guess there is not a lot of tables from the technical point of view (in the sense that it is possible to keep the info in memory). Then I suggest to collect the information into a Python dictionary where the table name is the key, and the info for the table would be stored as a list of the related rows.

The first pass of the rows will only build the dictionary. The second pass would traverse the dictionary and process the collected information. Somehing like this:

# Let's simulate the rows.
rows = [
    ('db1', 'dbo', 'tbl1', 'id',  'int',       1, 1,    None),
    ('db1', 'dbo', 'tbl1', 'txt', 'varchar',  30, 2,    None),
    ('db1', 'dbo', 'tbl1', 'dte', 'datetime',  3, 2,    None),
    ('db1', 'dbo', 'tbl2', 'id',  'int',       1, None, None),
    ('db1', 'dbo', 'tbl2', 'txt', 'varchar',  30, 2,    1)

# Build the dictionary named info (without comments, 4 lines only).
# The key could be a tuple of the first three elements. It could
# also composed string like 'db1.dbo.tbl1' -- I am showing that variant. 
# The x.join() iterates through the argument--the items must be
# of a string type--and joins them using the string value x
# (here the dot).
# The row[:3] is the same as row[0:3] and means slicing the
# first three elements of the row tuple. Better to say, it
# returns a tuple with elements from index 0 (including)
# to index 3 (excluding). The .join iterates through that tuple.
# Append each row to the list of tuples bound to the key. However,
# the key and list does not exist at first. Because of that, the
# operation info.setdefault(k, []) is used instead of info[k].
# It ensures that the empty list is returned first time.
info = {}        # init -- empty dictionary
for row in rows:
    k = '.'.join(row[:3])
    info.setdefault(k, []).append(row)

# Now traverse the dictionary and display/process the info. You can
# compose your SQL commands here.
for k in info:           # Order of keys is not preserved unless...
    for row in info[k]:         # Loop through the info for one table.
        print('    ', row[3:])  # slicing again

Open in new window

It prints on my console
     ('id', 'int', 1, 1, None)
     ('txt', 'varchar', 30, 2, None)
     ('dte', 'datetime', 3, 2, None)
     ('id', 'int', 1, None, None)
     ('txt', 'varchar', 30, 2, 1)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocAuthor Commented:
Thank you for this nice and clean example. Is kind of what I was looking for!
Thank you. I am glad I could help. ;)
ZberteocAuthor Commented:
I have another question you can look into:

thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.