3

I wrote a python script that store outputs from PostgreSQL in a CSV file, the part of this CSV file looks like,

enter image description here

And I wrote some codes to extract and multiple every second number in the element row by row, for example(look above photo), for the second row, 0.844 * 0.0 = 0; the fourth row, 0.844 * 0.0 * 0.0 * 0.0 = 0,

import sys, os
os.chdir('C:\Users\Heinz\Desktop')
print os.getcwd()

#set up psycopg2 environment
import psycopg2

#driving_distance module
query = """
    select *
    from shortest_path ($$
        select
            gid as id,
            source::int4 as source,
            target::int4 as target,
            pi::double precision as cost,
            pi_rcost::double precision as reverse_cost
        from network
        $$, %s, %s, %s, %s
    )
"""

#make connection between python and postgresql
conn = psycopg2.connect("dbname = 'TC_area' user = 'postgres' host = 'localhost' password = 'xxxx'")
cur = conn.cursor()

#count rows in the table
cur.execute("select count(*) from network")
result = cur.fetchone()
k = result[0] + 1                #number of points = number of segments + 1

#run loops
#import csv module
import csv
import tempfile
import shutil
rs = []
ars = []
i = 1
l = 1
filename = 'test01.csv'
with open(filename, 'wb') as f:
    while i <= k:
        while l <= k:
            cur.execute(query, (i, l, False, True))
            rs.append(cur.fetchall())
            element = list(rs)
            [a[-1] for a in element]
            product = reduce(lambda x,y: x * y, [a[-1] for a in element])
            writer = csv.writer(f, delimiter = ',')
            writer.writerow(product)
            rs = []
            l = l + 1
        l = 1
        i = i + 1


conn.close()

And the code above may create a CSV file,

enter image description here

Because there are always 0 in the last position in every element in every row, I got only 0 in the third column.

How to edit the following 2 lines in my original script？

[a[-1] for a in element]
product = reduce(lambda x,y: x * y, [a[-1] for a in element])

The first line extracts every third element in every subtuple, but I don't want to extract the last one in the last subtuple in a row.

My goal is to multiply every third element in the bracket except the last one if there are more than one bracket in a row, for example, the second row we want to get 0.844 as result; and if there are only one bracket in a row, we assign one for this row.

UPDATE#1

I modified my code like this, 

#run loops
#import csv module
import csv
import tempfile
import shutil
rs = []
ars = []
i = 1
l = 1
filename = 'test01.csv'
with open(filename, 'wb') as f:
    while i <= k:
        while l <= k:
            cur.execute(query, (i, l, False, True))
            rs.append(cur.fetchall())
            element = list(rs)
            [a[-1] for a in element]
            product = 1 if len(element) == 1 else reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]])
            writer = csv.writer(f, delimiter = ',')
            writer.writerow([product])
            rs = []
            l = l + 1
        l = 1
        i = i + 1

I put "product" in a bracket in the line writer.writerow([product]) in order to prevent the sequence expected error, and I got the CSV file containing 1 in every column,

enter image description here

UPDATE#2

I did a little change in the part of my script to test the length of element,

#run loops
#import csv module
import csv
import tempfile

rs = []
i = 1
l = 1
filename = 'test02.csv'
with open(filename, 'wb') as f:
    cur.execute(query, (2, 3, False, True))
    rs.append(cur.fetchall())
    element = list(rs)
    print len(element)
    [a[-1] for a in element]
    product = reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]], 1)
    writer = csv.writer(f, delimiter = ',')
    writer.writerow(element)
    rs = []

conn.close()

And here's the output,

len(element) = 1

enter image description here

Even though there are 2 elements in one row, but python returns len(element) = 1(Can also be proof by clicking field B1 in EXCEL, this field contains nothing)

I guess there are all 1 in the column A in last UPDATE because length of all elements are equal to 1, so the following line can't generate acceptable answers.

product = reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]], 1)

How to edit the script to get the true length of elements？

UPDATE#3

I want to get a product matrix directly, thus I wrote this script,

import sys, os
os.chdir('C:\Users\Heinz\Desktop')
#print os.getcwd()

#set up psycopg2 environment
import psycopg2

#pgRouting module
query = """
    select *
    from shortest_path ($$
        select
            gid as id,
            source::int4 as source,
            target::int4 as target,
            pi::double precision as cost,
            pi_rcost::double precision as reverse_cost
        from network
        $$, %s, %s, %s, %s
    )
"""

#make connection between python and postgresql
conn = psycopg2.connect("dbname = 'TC_area' user = 'postgres' host = 'localhost' password = 'xxxx'")
cur = conn.cursor()

#count rows in the table
cur.execute("select count(*) from network")
result = cur.fetchone()
k = result[0] + 1                #number of points = number of segments + 1

#import csv module
import csv
import tempfile
import shutil

#run loops
rs = []
i = 1
l = 1
filename = 'rs.csv'
with open(filename, 'wb') as f:
    writer = csv.writer(f, delimiter = ',')
    while i <= k:
        while l <= k:
            cur.execute(query, (i, l, False, True))
            rs.append(cur.fetchall())
            l = l + 1
        l = 1
        writer.writerow(rs)
        rs = []
        i = i + 1


conn.close()

Here's the look in the CSV file,

enter image description here

This time every field in one row contains the output of the query line,

query, (i, l, False, True)

with different l, for example,

  • field A1 contains [(1, -1, 0.0)]
  • field B1 contains [(1, 1, 0.844), (2, -1, 0.0)]

So now I need to count how many elements in every field in every row and use the answer script provided by mtadd(thanks) to calculate products.

Can I get the number of element in a field?

Improve this question
4
  • 1
    Last paragraph, what do you mean by "every second object in the bracket"? Do you mean to skip every other subtuple, or do you mean the second element of each subtuple? Your code example extracts the third element instead of the second element of each subtuple, which is also confusing, Please clarify. In any case, this may be what you want: product = 1 if len(element) == 1 else reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]]) Commented Apr 20, 2014 at 5:11
  • use print statements to verify the contents of element are correct before calculating product Commented Apr 20, 2014 at 5:55
  • @mtadd the contents of element are correct, as I showed in the first photo of my post. Commented Apr 20, 2014 at 6:51
  • @mtadd I found that all len(element) = 1 so the script can't generate right answers. And I have edited my post. Commented Apr 20, 2014 at 13:21

2 Answers 2

Reset to default
1

Since writerow takes a list as a parameter, the contents of element in your example is actually [[(2, 2, 0.0), (3, -1, 0.0)]], or a list of list of subtuples. The inner list of subtuples is returned from fetchall.

Let's take a look at your code, line-by-line, and what it generates for your example spreadsheet in update 2.

cur.execute(query, (2, 3, False, True))
temp = cur.fetchall()
print temp

This would return a list of tuples [(2,2,0.0), (3,-1,0.0)], what you actually are expecting. The problem starts at:

rs.append(temp)

When you call rs.append, you're encapsulating the list of tuples returned from the database in another list, creating a list of list of tuples, e.g. [[(2,2,0.0), (3,-1,0.0)]]. The outer list has a length of 1, i.e., it contains 1 list of tuples. 

element = list(rs)

This creates a new list that is a shallow copy of rs, but otherwise, is the same structure as rs, containing a list of list of tuples.

So, changing your code to something like following should fix your problem:

filename = 'test02.csv'
with open(filename, 'wb') as f:
    cur.execute(query, (2, 3, False, True))
    element = cur.fetchall()
    print len(element)
    if len(element) == 1:
        product = [1]
    else:
        product = reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]], 1)
    writer = csv.writer(f, delimiter = ',')
    writer.writerow(element)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thank you very much, I can calculate the product successfully, but I want to get the product matrix directly, thus I edited my post. Feel free to give me suggestions!
1

After doing:

rs.append(cur.fetchall())
element = list(rs)

The element becomes a list of length 1, with the only item in it being the list of all the rows from the query. You need to change it to:

element = cur.fetchall()

To compute the product use:

product = reduce(lambda x,y: x*y, [a[-1] for a in element[:-1]],1)

Setting the initializer of reduce to 1

Improve this answer

1 Comment

I tried your code but got the same results in CSV file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.