Python put Database records in Namedtuple

Question

I'm trying to write some code in python (2.7) doing this:

Open a database in sqlite
Do a query to the database, getting some results. The database has more than one table and i need the records from different tables: database is like this:
- data.db ---> [table1[col1, col2, col3], table2[col1, col2, col3]]
Iterate through the results
Do something on the results (for exaple in a record there is a date that need decoding)
Store all the records in a namedtuple for further access

Since now i have achieved part 1, 2, 3 and 4, but i can't figure out how to store the results in a namedtuple. Assume that on every iteration i store the data that i need in the namedtuple in temporary variables:

for result in results:
var1 = table1.col1
var2 = table2.col1
var3 = table3.col1

(now i want to do something with the variable, but thats' not the problem, and store the 3 variables in a namedtuple)

contacts = namedtuple('contacts', 'Z_PK ZFULLNAME ZPHONE ZTEXT ZDATE')

    if has_contacts_sqlite:

        # reading contacts from database
        # 1st step: ZWAPHONE table
        query = "SELECT * FROM ZWAPHONE"
        self.tempcur.execute(query)
        tempcontacts = self.tempcur.fetchall()

        for contact in tempcontacts:
            id = contact.Z_PK
            contact_key = contact.ZCONTACT
            favorite_key = contact.ZFAVORITE
            status_key = contact.ZSTATUS
            phonenum = contact.ZPHONE

            # 2nd step: name from ZWACONTACT table
            query = "SELECT * FROM ZWACONTACT WHERE Z_PK=?;"
            self.tempcur.execute(query, [contact_key])
            contact_entry = self.tempcur.fetchone()
            if contact_entry == None:
                name = "N/A"
            else:
                name = contact_entry.ZFULLNAME

            # 3rd step: status from ZWASTATUS table
            query = "SELECT * FROM ZWASTATUS WHERE Z_PK=?;"
            self.tempcur.execute(query, [status_key])
            status_entry = self.tempcur.fetchone()
            if status_entry == None:
                text = "N/A"
                date = "N/A"
            else:
                text = status_entry.ZTEXT
                date = self.formatDate(status_entry.ZDATE)

            #print ("%s" %(id))
            #print ("%s" %(name.encode(sys.stdout.encoding, errors='replace')))
            #print ("%s" %(phonenum.encode(sys.stdout.encoding, errors='replace')))
            #print ("%s" %(text.encode(sys.stdout.encoding, errors='replace')))
            #print ("%s" %(date))

            contact = contacts(id, name, phonenum, text, date)


print contacts
for line in contacts:
    print line

Martijn Pieters · Accepted Answer · 2013-11-20T14:23:10.340

3

A namedtuple() is nothing but a class generated with a factory function:

SomeRowResult = namedtuple('SomeRowResult', 'var1 var2 var3')

Here SomeRowResult is a class object (a subclass of tuple), and calling it will create instances of the class:

for result in results:
    result = SomeRowResult(table1.col1, table2.col1, table3.col1)

If you wanted to have a list of these results, you need to explicitly build that list:

all_results = []
for result in results:
    result = SomeRowResult(table1.col1, table2.col1, table3.col1)
    all_results.append(result)

edited Nov 20 '13 at 14:23

answered Nov 20 '13 at 13:12

Martijn Pieters

1,048,767
296
4,058
3,343

Thanks for you reply. I added my code. Is there some syntax error? Because it's not still functioning. For testing i'm trying to iterate through "contacts" and print the results but it says: TypeError: 'type' object is not iterable. – Rigel Nov 20 '13 at 13:55
@user3013106: The code you posted should just work; are you certain you didn't assign anything else to `contacts` anywhere in your code? – Martijn Pieters Nov 20 '13 at 13:57
@user3013106: also, that `TypeObject` looks entirely unrelated to a `namedtuple`. What is the traceback for that? What line of code throws that error, exactly? – Martijn Pieters Nov 20 '13 at 14:06
Nope. I'm testing with this code: print contacts return: and for line in contacts: print line and it returns that is not iterable (sorry, i can't figure out to format code in comments in a readable form) – Rigel Nov 20 '13 at 14:09
@user3013106: That is a *different* question. A `namedtuple` class is not iterable. What are you trying to do there? Did you want to loop over the attribute names? – Martijn Pieters Nov 20 '13 at 14:13
@user3013106: That loop (`for line in contacts:`) is not in your posted code. – Martijn Pieters Nov 20 '13 at 14:14
@user3013106: What are you expecting that last loop to *do*? You have a namedtuple `contacts`, that is a class, not the list of instances you made. – Martijn Pieters Nov 20 '13 at 14:22
I need to create something that contains all the data that i fetch, that is iterable and accessible by the attributes (so when i loop through it i can access them through contact.Z_PK, contact.ZPHONE, etc). Maybe i misunderstood the namedtuple class and need to create a list that contains namedtuple? – Rigel Nov 20 '13 at 14:33
1

@Rigel: Yes, you can loop over `all_results`; each item is one `namedtuple` instance, with all the attributes you defined. You cannot, however, loop over the class that created the instances. That'd be like trying to loop over `dict` or `tuple` to get all dictionaries or tuples in your program ever created. Python doesn't work like that. – Martijn Pieters Nov 20 '13 at 14:37
I finally did it. I created a list containing all the results and now it works just fine. All the problems were my bad understanding of the class. Thank you very much! – Rigel Nov 20 '13 at 15:00

Python put Database records in Namedtuple

1 Answers1

Linked