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

Question

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)

Why don't you just SELECT id, text FROM mytable ORDER BY id ASC, and then walk through the result set, based on the user's input? Failing that, make a function that fetches the text for the given id. — Kingsley
– Kingsley, Commented Oct 19, 2018 at 21:56

Kingsley · Accepted Answer · 2018-10-19 23:19:25Z

1

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

edited Oct 19, 2018 at 23:19

answered Oct 19, 2018 at 22:07

Kingsley

15k5 gold badges38 silver badges60 bronze badges

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

1 Comment

ByRequest Over a year ago

Thanks for the detail in how to break this out into functions. It's certainly cleaner, helped me learn and pointed me in a better direction to proceed. The only thing I am still struggling with in here is how I can assign the value from a column in the table to a variable to be passed back in. I'll work with this new script for a while and if I can not figure it out append something here.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related