CODEWITHSUNDEEP
pythonsqlitepython-3.x
HotDogCannon
HotDogCannon

Reputation: 2386

Iterating through python sqlite database

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!!!

Upvotes: 1

Views: 9961

Answers (2)

nanithehaddock
nanithehaddock

Reputation: 257

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"

Upvotes: 0

Daniel Roseman
Daniel Roseman

Reputation: 599590

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()

Upvotes: 5

Related Questions