CODEWITHSUNDEEP
pythonsqlsqlite
Styx1337
Styx1337

Reputation: 37

SQLite3 pass column argument as variable

I am trying to create an SQL request in Python that should get depending on the mode, the entries before today from different columns. Therefor I want to create a SQL request that takes different arguments of columns. Unfortunately I get always empty results as soon as I run the SQL request with variables. (tried it with "?" as well as with the "dict method")

def amount_of_testing_data(crs, mode):
    if mode == "kanji_read":
        col = "next_date_kanji_reading"
    elif mode == "kanji_write":
        col = "next_date_kanji_writing"
    elif mode == "kana_read":
        col = "next_date_kana_reading"
    elif mode == "kana_write":
        col = "next_date_kana_writing"
    else:
        return ("Error")

    values = {"col_": col, "date_": datetime.datetime.now().isoformat()}
    print(values)

    crs.execute("""SELECT ID FROM vocabulary WHERE Column=:col_ <= :date_""", values)
    return len(crs.fetchall())

If I put the tabel directly in the request, the SELECT returns the correct amount

def amount_of_testing_data(crs, mode):

    value = {"date_": datetime.datetime.now().isoformat()}

    if mode == "kanji_read":
        crs.execute("""SELECT ID FROM vocabulary WHERE next_date_kanji_reading <= :date_""", value)
    elif mode == "kanji_write":
        crs.execute("""SELECT ID FROM vocabulary WHERE next_date_kanji_writing <= :date_""", value)
    elif mode == "kana_read":
        crs.execute("""SELECT ID FROM vocabulary WHERE next_date_kana_reading <= :date_""", value)
    elif mode == "kana_write":
        crs.execute("""SELECT ID FROM vocabulary WHERE next_date_kana_writing <= :date_""", value)
    else:
        return ("Error")

    return len(crs.fetchall())

Is it possible to make the column as a variable?

Upvotes: 0

Views: 774

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169414

You cannot parametrize column names, but you can string substitute them.
For the parameter substitution be sure to use the right placeholder for SQLite3 which is a ?.
Then the second (2nd) argument to .execute() needs to be a sequence; here it is a one-tuple.

qry = """SELECT ID FROM vocabulary WHERE {col_} <= ?""".format(**values) 
crs.execute(qry, (datetime.datetime.now().isoformat(),))

Upvotes: 2

Related Questions