lundi 17 juillet 2017

Python: Using variables within sqlite3 for random selection

I have begun to learn about sqlite3 in python, and I am trying to make a small program that will pull a a random exercise from each category within my database. My question is two fold: First, in my code, the random_select() function I have selects one exercise at random from one column. How can I input a parameter into the function definition that will allow me to do this without using c.execute multiple times? I would like to have def random_select(exercise), then input which exercise I want it to query. Second, when the exercises print out, they come with the brackets and quotes ('Front Squat',). How can i get it to only print out the word itself. I am doing this mostly for experience, so if you know of a good tutorial I should refer to to answer these questions instead of outright handing me the answer, that would be just as welcome as a flat out answer :)

Below is my code (it is pretty messy right now, as I'm trying different aspects of sqlite, but the def random_select() is the main focus of my question.

import sqlite3

conn = sqlite3.connect('exercises.db')
conn2 = sqlite3.connect('exercises_2.db')

c = conn.cursor()
c2 = conn2.cursor()

def create_table():
    c.execute('CREATE TABLE IF NOT EXISTS exercises(legs TEXT, chest TEXT, back TEXT, abdominals TEXT, arms TEXT, '
              'misc TEXT)')
    c2.execute('CREATE TABLE IF NOT EXISTS exercises_2(legs TEXT, chest TEXT, back TEXT, abdominals TEXT, arms TEXT, '
               'misc TEXT)')
def data_entry():
    c.execute("INSERT INTO exercises VALUES('Squats', 'Bench Press', 'Dumbbell Row', 'Roman Chair', 'Barbell Curl', "
              "'Calf Raises')")
    c2.execute("INSERT INTO exercises_2 VALUES('Squats', 'Bench Press', 'Dumbbell Row', 'Roman Chair', 'Barbell "
               "Curl', 'Calf Raises')")

    conn.commit()
    conn2.commit()
    c.close()
    c2.close()
    conn.close()
    conn2.close()

def dynamic_data_entry():
    legs = str(input("Please input a leg exercise: "))
    chest = str(input("Please input a chest exercise: "))
    back = str(input("Please input a back exercise: "))
    abdominals = str(input("Please input an abdominal exercise: "))
    arms = str(input('Please input an arm exercise: '))
    misc = str(input("Please input a miscellaneous exercise: "))
    c.execute("INSERT INTO exercises_2 (legs, chest, back, abdominals, arms, misc) VALUES (?, ?, ?, ?, ?, ?)", (legs, chest, back, abdominals, arms, misc))
    conn.commit()


def random_select():
    c.execute('SELECT legs FROM exercises ORDER BY Random() LIMIT 1')
    data = c.fetchone()  #fetchone can be used as well
    print(data)
    c.execute('SELECT chest FROM exercises ORDER BY Random() LIMIT 1')
    data = c.fetchone()
    print(data)
    c.execute('SELECT back FROM exercises ORDER BY Random() LIMIT 1')
    data = c.fetchone()
    print(data)
    c.execute('SELECT abdominals FROM exercises ORDER BY Random() LIMIT 1')
    data = c.fetchone()
    print(data)
    c.execute('SELECT arms FROM exercises ORDER BY Random() LIMIT 1')
    data = c.fetchone()
    print(data)
    c.execute('SELECT misc FROM exercises ORDER BY Random() LIMIT 1')
    data = c.fetchone()
    print(data)
    conn.commit()

# c.execute('SELECT * FROM exercises_2) then data=(len(c.fetchall))  to find if data is more than 4 columns.
#Make if statement (if more than 4 columns, delete table to start over.)
def delete_database():
    c.execute('DELETE FROM exercises')
    conn.commit() 

#create_table()
#data_entry()
#delete_database()
#dynamic_data_entry()
random_select()
c.close()
conn.close()




Aucun commentaire:

Enregistrer un commentaire