CODEWITHSUNDEEP
pythondatabasesearch
abkai
abkai

Reputation: 591

Search database

Furthering my study of database use in python, I have come up with the following code based on Bryan Moyles excellent example:

from os   import system as cmd
from time import sleep  as sleep
try:
    from sqlite3 import dbapi2 as sqlite
except ImportError:
    from pysqlite2 import dbapi2 as sqlite

class search_db:
    def __init__(self, db, name):
        self.db = db_connection
        self.name = name
        self.dbc = self.db.cursor()

    def __getitem__(self, item):
        self.dbc.execute("select * from %s limit %s, 1" % (self.name, item))
        return self.dbc.fetchone()

def search():
    customer_id  = []
    customer_ids = search_db(db_connection, "customers")
    for i in range(10000):
        if not "None" in str(customer_ids[i]): customer_id.append(customer_ids[i])
    return customer_id

## init ##
db_name = 'customers'
db_connection = sqlite.connect('program.db')
db_curs = db_connection.cursor()

search()

Sure, it's nothing fantastic, and I know I could probably optimise it a tad more; but as you can see, I've been trying hard to figure out how to search the database. I managed to retrieve entries that contain information in the database, which pulls out an entry number. I was thinking of using if loops to search the appended list values for a "GGS-number" entry, then using regular expressions to extract the line number - then printing the information relevant to the customer that the GGS number applies to. Has anyone got any ideas how I could do this in a better way? Also, I have a for i in range(10000), is there a more optimised form of this?

Upvotes: 1

Views: 14591

Answers (1)

alexis
alexis

Reputation: 50220

If this is all intended to retrieve the contents of the table "customers", you are doing it wrong. SELECT will give you a table with no gaps; so if the only IDs in use are 10 and 101, your query results will have length 2 and contain the two existing records in rows 0 and 1. It looks like you're trying to skip over empty records, but there aren't any.

But you're making this much too complicated; you've chosen the wrong example to emulate. What you have runs a SELECT query that returns the n'th row from a table, wrapped into a class that allows you to pretend you're dealing with a very slow array. Ditch it and start over.

If you want the entire contents of the customers table, use a single query and retrieve them with fetchall():

import sqlite3
conn = sqlite3.connect('program.db')
curs = conn.cursor()
ro = curs.execute('select * from customers')
customers = ro.fetchall()

If you need to fetch a particular record, let the database handle that by adding a WHERE clause to the query. Almost always you retrieve records by ID (or other attributes), not by their position in the table.

My advice: Keep the python/sqlite scaffolding minimal. Set up a connection, run a query, fetch the results one by one or all together. That's all. And curs.rowcount to find out how many result rows there are before fetching them. Everything else is done more simply and efficiently by an appropriate SQL command. So read up on SQL, not on python's libraries.

Upvotes: 1

Related Questions