Python Forum

Pages: 1 2

Thanks deanhystad. I copied and pasted your code and ran it to see how it worked and I get an error that says:

Error:
TypeError: can only concatenate tuple (not "List") to tuple

I use this baby every week during term, works like clock-Python-work!

Change weeknr for Recno and you have your list!

Of course, you don't need clas, but if you have various similarly named tables, maybe something like it.

import pymysql 

def mysqlRemoteCW(clas, weeknr): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='111.222.333.444', 
        user='me',  
        password = 'secret', 
        db='allstudentsdb', 
        ) 
      
    cur = conn.cursor()

    # Select query 
    sql = f"SELECT studentnr, score FROM allstudentsAnswers{clas}CW WHERE weeknr = %s"
    cur.execute(sql, (weeknr,))
    output = cur.fetchall()    
    # To close the connection 
    conn.close()
    return output

results = mysqlRemoteCW(clas, weeknumber)

I knew I would eventually have to make a database and test the code. Should have done that right away. Sorry.

This works:

import sqlite3

def getRecord(srchName, columns):
    """Return columns from first matching record"""
    # Wrap column names in quotes and separete by ", ".  Am wrapping
    # column names in quotes because column name might have special
    # meaning in an SQL query, like "group" for example.
    columns = ", ".join([f"\"{column}\"" for column in columns])
    query = f"SELECT {columns} FROM dino WHERE name = ?"
    c.execute(query, (srchName,))
    return c.fetchone()

conn = sqlite3.connect('dinobase.db')
c = conn.cursor()

id, group = getRecord("Entelodon",  ["recNo", "group"])
print(id, group)

conn.close()

I learned a couple things getting this to work.
1. You cannot use placeholders for column names. My idea of using "SELECT ?, ? FROM dino WHERE name = ?" did not work because you cannot use a placeholder to specify the columns you want returned.

2. "group" has special meaning. When I tried to execute "SELECT recNo, group FROM dino WHERE name =?" I got an error.

Error:
sqlite3.OperationalError: near "group": syntax error

I think it saw "group" and started looking for "GROUP BY". There are a lot of words that have special meaning in a query, so I decided to wrap all column names in double quotes: "SELECT "recNo", "group" FROM dino WHERE name = ?" works fine.

And the error in my previous post was from trying to add a list to a tuple: columns + [srchName]. Since I cannot use placeholders for column names, this is no longer a problem.

Thanks deanhystad for your time and patience.

Cheers!

Pages: 1 2

Columbo

Pedroski55

deanhystad

Columbo