CODEWITHSUNDEEP
pythonsqlsqlite
HotDogCannon
HotDogCannon

Reputation: 2386

Iterating through an SQL database

I am a newbie with SQL and I have an application in which i'd like to iterate through a set of tuples of format (p1,p2,id) and compare the current tuple to the previous one.

If the p1 of the current tuple matches the p2 of the previous:

here is an example that illustrates my application:

import sqlite3 as lite

vectors = [
(1,2,1),
(2,3,2),
(3,4,3),
(4,5,4),
(6,7,5),
(7,8,6),
(8,9,7),
(9,10,8),
(11,12,9),
(12,13,10),
(13,14,11),
(15,16,12)
]

### define database object and cursor objects
vec_data = lite.connect('vecs.db')

cur = vec_data.cursor()
editor_cursor = vec_data.cursor()

### put python objects in SQL
cur.execute("DROP TABLE IF EXISTS vecs")
cur.execute("CREATE TABLE vecs(p1,p2,id INTEGER PRIMARY KEY)")
cur.executemany("INSERT INTO vecs VALUES(?, ?, ?)", vectors)

### iterate through and 'merge' vecs
cur.execute("SELECT * FROM vecs ORDER BY p1 ASC")
old_vec = None
while True:

    try:
        old_vec = vec
    except NameError:
        old_vec = None

    vec = cur.fetchone()
    if vec == None:
        break

    if (vec is not None) and (old_vec is not None):

        old_vec_p1 = old_vec[0]
        old_vec_p2 = old_vec[1]
        vec_p1 = vec[0]
        vec_id = vec[2]
        old_vec_id = old_vec[2]

        if (old_vec_p2 == vec_p1):
            editor_cursor.execute("UPDATE vecs SET p1=? WHERE id=?",(old_vec_p1,vec_id))
            editor_cursor.execute("DELETE FROM vecs WHERE id=?",(old_vec_id,))

cur.execute("SELECT * FROM vecs")       
while True:
    vec = cur.fetchone()
    if vec == None:
        break
    print(vec)

vec_data.close()

...now given my input, the output that I would want is:

(1, 5, 4)
(6, 10, 8)
(11, 14, 11)
(15, 16, 12)

...but instead I get:

(3, 5, 4)
(8, 10, 8)
(12, 14, 11)
(15, 16, 12)

The equivalent of what i want to do (but done purely in python) is:

vectors = [
[1,2,1],
[2,3,2],
[3,4,3],
[4,5,4],
[6,7,5],
[7,8,6],
[8,9,7],
[9,10,8],
[11,12,9],
[12,13,10],
[13,14,11],
[15,16,12]
]

i=0
while i < (len(vectors)-1):
    vec = (vectors[i+1])
    old_vec = (vectors[i])

    old_vec_p1 = old_vec[0]
    old_vec_p2 = old_vec[1]
    vec_p1 = vec[0]
    vec_id = vec[2]
    old_vec_id = old_vec[2]

    if (old_vec_p2 == vec_p1):
        vectors[i+1][0] = old_vec_p1
        del vectors[i]
    else:
        i += 1


for i in vectors:
    print i

So do I have a misunderstanding of SQL or do I have a bug in my code?

Upvotes: 0

Views: 209

Answers (2)

Tito
Tito

Reputation: 44

    vecs = cur.execute("SELECT * FROM vecs ORDER BY p1 ASC")
    old_vec = None
    for vec in vecs:
        if vec and old_vec:
            old_vec_p1 = old_vec[0]
            old_vec_p2 = old_vec[1]
            vec_p1 = vec[0]
            vec_id = vec[2]
            old_vec_id = old_vec[2]
            if (old_vec_p2 == vec_p1):
                editor_cursor.execute("UPDATE vecs SET p1=? WHERE id=?",(old_vec_p1,vec_id))
                editor_cursor.execute("DELETE FROM vecs WHERE id=?",(old_vec_id,))
        old_vec = vec

    vecs = cur.execute("SELECT * FROM vecs")       
    print vecs.fetchall()
    vec_data.close()

Looks like your question is answered already, where vec was not being updated. I enjoy looking at different solutions, so I hope this can help you. You can simply iterate over the returned rows. 


    if vec and old_vec:

is similar to

    if (vec is not None) and (old_vec is not None):

Upvotes: 0

tzunghaor
tzunghaor

Reputation: 1035

After vec = cur.fetchone() vec is in your hands: you update the row in the database, but vec will not change.

Add vec = (old_vec_p1, vec[1], vec_id) after (or before) the database changes.

Upvotes: 1

Related Questions