CODEWITHSUNDEEP
pythonpython-3.xpostgresqlpostgresql-9.6
ByRequest
ByRequest

Reputation: 311

Using Python to select a dynamic row in a Postgresql database based on an integer provided in a column from the last select

I just started working with Python today, so I am at a novice level.

I am attempting to make a database driven "decision tree". Once complete, it will operate almost like a "choose your own adventure" story as seen in Python examples online.

I have a table in PostgreSQL with the following columns: id, textfield, nextitem

Here is what I am looking to do: -The script will start at some record in the table. For all purposes right now, we'll just start it at id 1. We'll query this first row (or whatever row may be the starting point in the future). So if I were to reference this directly in Postgres, I would simply SELECT * FROM mytable or SELECT * FROM mytable WHERE id=1

-If column [nextitem] is not null, proceed to the integer in the column. So, if SELECT * FROM mytable WHERE id=1 has a value of '4' in [nextitem], the next query should be SELECT * FROM mytable WHERE id=4. This process would repeat until [nextitem] is NULL.

I am using Python3 on Ubuntu Server 16.04.3 with PostgreSQL 9.6 and psycopg2.

I believe what I need to do is pull the value from the column out into a variable then allow python to feed it back in for a future query than loop it.

Here is what I have so far (it needs a lot of cleaning as I have made a mess by trying different things in the area I was stuck - my hangup seems to be on separating out the data from the query into the python variable and feeding it back in):

import psycopg2
try:
    connect_str = "dbname='mydatabase' user='myuser' host='localhost' " + \
                  "password='mypassword'"
    # establish a connection
    conn = psycopg2.connect(connect_str)
    # cursor that to execute queries
    cursor = conn.cursor()
    # start at the beginning, select the first text field
    sql = "SELECT textfield FROM mytable WHERE id=1"
    cursor.execute(sql)
    rows = cursor.fetchall()
    print(rows)
    cont = raw_input('Type Accept Continue')
    # if user accepts it is ok to proceed, advance to display data from next textfield
    if cont=='Accept':
        print("Accepted")
        sqn = "SELECT textfield, nextitem FROM mytable WHERE id=2"
        cursor.execute(sqn)
        rows = cursor.fetchall()
        print(rows)
        result_set = cursor.fetchall()
        #ideally, this should grab the integer in the [nextitem] column from last query and select the row corresponding to the integer
        for row in result_set:
        #print the integer to test the value and make sure it is correct
            print "%s" % (row["nextitem"])
        #attempt to assign this integer to a variable?
        x=["nextitem"]
        #attempt feeding the integer previously selected in [nextitem] into the next query
        sqv = "SELECT text FROM mytable WHERE id=%s"
        cursor.execute(sqv,x)
        result = cursor.fetchall()
        print(result)
    else:
        print("Rejected or Not Accepted")
except Exception as e:
    print("No Connection Available")
    print(e)

Upvotes: 1

Views: 1920

Answers (1)

Kingsley
Kingsley

Reputation: 14926

Split your database operations out into separate functions. That will make the program flow much easier.

Then simply loop around, fetching the message text using the input ID, display the message, get the input -> next ID, repeat.

You don't need the "Accept" condition, just make it part of the first text (on mytable.id=1): "Enter 2 to Accept, q to Quit".

def openDatabase():
    connect_str = "dbname='mydatabase' user='myuser' host='localhost' password='mypassword'"
    try:
        conn = psycopg2.connect(connect_str)
    except:
        sys.stderr.write("Unable to connect to database\n")
        conn = None
    return conn

def fetchSQL(db_conn, sql_query):
    cursor = db_conn.cursor()
    # start at the beginning, select the first text field
    cursor.execute(sql_query)
    rows = cursor.fetchall()
    #print(rows)
    return rows

def fetchItemText(db_conn, item_index):
    query = "SELECT textfield FROM mytable WHERE id=" + str(item_index)
    rows = fetchSQL(db_conn, query)
    if (len(rows) > 0):
        return rows[0][0] # Maybe just be rows[0]? Coding of the top of my head
    else:
        return ""

### Main
db = openDatabase()
if (db != None):
    # do some stuff
    finished = False
    user_choice = 1
    # Prompt the user with the text, get their input
    while (not finished):
        prompt = fetchItemText(db, user_choice)
        user_choice = raw_input(prompt + "\n>>>")
        if (user_choice == 'q'):
            finished = True
        else:
            user_choice = int(user_choice)  # TODO handle errors

Upvotes: 1

Related Questions