Iterating through python sqlite database

Question

I am trying to iterate through an SQLite database and perform checks or operations on the objects in the list. I need to use a database because the eventual number of objects will be quite large and all the operations are serial in nature (after basic sorting).

My question is how can I iterate through a list and after checking an object for certain qualities put it into a new database object? I would like to perform several serial 'checks' where at most two objects are brought into memory at a time and then re-assigned.

Below is a sample of my code. When I run the last operation I cannot 're-run' the same loop. How can I, instead of just printing the object, save it to a new database?

import os
import sqlite3 as lite
import sys
import random
import gc
import pprint

def make_boxspace():

    refine_zone_cube_size = 1

    refine_zone_x1 = 1*refine_zone_cube_size
    refine_zone_y1 = 1*refine_zone_cube_size
    refine_zone_z1 = 1*refine_zone_cube_size
    refine_zone_x2 = refine_zone_x1+(2*refine_zone_cube_size)
    refine_zone_y2 = refine_zone_y1+(1*refine_zone_cube_size)
    refine_zone_z2 = refine_zone_z1+(1*refine_zone_cube_size)

    point_pass_length = (1.0/4.0)

    outlist = []
    for i in range(int((refine_zone_x2-refine_zone_x1)/point_pass_length)):
        for j in range(int((refine_zone_y2-refine_zone_y1)/point_pass_length)):
            for k in range(int((refine_zone_z2-refine_zone_z1)/point_pass_length)):

                if (random.random() > 0.5):
                    binary = True
                else:
                    binary = False

                if binary:
                    x1 = point_pass_length*i
                    y1 = point_pass_length*j
                    z1 = point_pass_length*k
                    x2 = x1+point_pass_length
                    y2 = y1+point_pass_length
                    z2 = z1+point_pass_length

                    vr_lev = int(random.random()*3)

                    outlist.append([\
                    float(str("%.3f" % (x1))),\
                    float(str("%.3f" % (y1))),\
                    float(str("%.3f" % (z1))),\
                    float(str("%.3f" % (x2))),\
                    float(str("%.3f" % (y2))),\
                    float(str("%.3f" % (z2))),\
                    vr_lev
                    ])

    return outlist


### make field of "boxes"
boxes = make_boxspace()

### define database object and cursor object
box_data = lite.connect('boxes.db')
cur = box_data.cursor()

### write the list in memory to the database
cur.execute("DROP TABLE IF EXISTS boxes")
cur.execute("CREATE TABLE boxes(x1,y1,z1,x2,y2,z2,vr)")
cur.executemany("INSERT INTO boxes VALUES(?, ?, ?, ?, ?, ?, ?)", boxes)

### clear the 'boxes' list from memory
del boxes

### re-order the boxes
cur.execute("SELECT * FROM boxes ORDER BY z1 ASC")
cur.execute("SELECT * FROM boxes ORDER BY y1 ASC")
cur.execute("SELECT * FROM boxes ORDER BY x1 ASC")

### save the database
box_data.commit()

### print each item
while True:
    row = cur.fetchone()
    if row == None:
        break
    print(row)

Thanks guys!!!

This is the not the sample of your code, this is the entire code :p — thefourtheye
– thefourtheye, Commented Jan 13, 2014 at 14:00
when i run this code, it creates a new sqlite3 database on my hdd called boxes.db full of "boxes"...what isn't working? — mmdanziger
– mmdanziger, Commented Jan 13, 2014 at 14:06
You may want to consider using a ORM such as SQLAlchemy. Besides providing a more intuitive interface to the database, it has the advantage of abstracting the database engine (sqlite), so you can use others (postgresql, mysql, ...) in the future, if you need to — loopbackbee
– loopbackbee, Commented Jan 13, 2014 at 14:07
Back up a second. Do you want the database table boxes the way it is, or is the ultimate goal to re-order the elements in boxes? — unutbu
– unutbu, Commented Jan 13, 2014 at 14:08

Daniel Roseman · Accepted Answer · 2014-01-13 14:06:15Z

5

I really don't understand what you're asking, but I think you have some fairly fundamental misunderstandings of SQL.

SELECT... ORDER BY does not "order the table", and running commit after a SELECT does not do anything. Sending three separate SELECTs with different ORDER BY but only running fetch once also does not make any sense: you'll only fetch what was provided by the last SELECT.

Perhaps you just want to order by multiple columns at once?

result = cur.execute("SELECT * FROM boxes ORDER BY z1, y1, x1 ASC")
rows = result.fetchall()

answered Jan 13, 2014 at 14:06

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HotDogCannon Over a year ago

ok, thanks for that! yes, i am a newbie with SQLite. Ideally, I would like to iterate through the 'list' (table object) and compare two 'adjacent' objects at a time, and then write to a completely new database during this loop.

corporateWhore · Accepted Answer · 2020-09-12 14:17:06Z

0

I think connecting to the sqlite3 database is as simple as we know that. since we are accessing database queries and results directly from the database, we need to take all the results in a list with fetchall() method and iterate through that list. so that you can get any number of results in multiple list with single connections. following is the simple python code

conn = sqlite3.connect("database file name")
cur = conn.cursor()
cur.execute("your query")
a_list = cur.fetchall()
for i in a_list:
    "process your list"
"perform another operation using cursor object"

edited Sep 12, 2020 at 14:17

corporateWhore

6352 gold badges12 silver badges25 bronze badges

answered Jan 15, 2014 at 5:34

nanithehaddock

2571 silver badge9 bronze badges

2 Comments

Elijah W. Gagne Over a year ago

cur.getchall() should be cur.fetchall()

whatacold Over a year ago

a more concise way: for i in cur.execute("your query"): ...

Collectives™ on Stack Overflow

Iterating through python sqlite database

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related