MySQL语句中缺少操作数

Question

MySQL模块在查找4个操作数时出现参数错误。我只是不知道它在找哪个操作数。它适用于某些情况类型，而不适用于其他情况。错误行号（底部修剪，表示MySQL库）：

Traceback (most recent call last):
  File "/Users/christoph/PycharmProjects/physicianWorkQueueProject/physicianWorkQueueProject.py", line 158, in <module>
    parse()
  File "/Users/christoph/PycharmProjects/physicianWorkQueueProject/physicianWorkQueueProject.py", line 138, in parse
    c.execute(getPhysiciansql_cmd)
...
mysql.connector.errors.DataError: 1241 (21000): Operand should contain 4 column(s)

---

#!/usr/bin/python
##################################################
# This is a prototype pathologist work queue management system
# The program calls out to a database of pathologists, their specialities,
# and an index of case types and their intended specialists
#
# Usage: Run at your command line. You will then enter case numbers (which aren't validated (currently))
# and case types (which are validated). The program will distribute the entered case as follows:
#
# If the case is intended for generalists, a system that amounts to names being pulled from a hat
# is employed. When a name is selected (at random), the case is entered into that pathologists' work queue.
# That pathologists' name is not returned to the pool.
# The cycle will then repeat with a random name picked every time, in this same way, for generalist-requiring cases,
# until the entirety of names have been pulled. At this point, all names are returned to the pool and the whole cycle
# begins again
#
# For the specialist requiring cases, the name-draw system is bypassed and the case is directly entered
# into the pathologists' work queue.

# 16 Aug 2018
# My Real Name
##################################################

import mysql.connector as mariaDB
import time
import pandas as pd
from random import choice


def distributefairly(inputCaseNumber, inputcasetype):
    # function distrbutefairly does a draw out of a hat, with each name being pulled and the pot shrinking until none are
    # left at which point all names are added back

    c.execute("SELECT physicianName FROM physicianNamesSpecialties;")
    originalPhysicianList = c.fetchall()
    physicianList = originalPhysicianList

    # print("counter at:",count)

    chosenPhysician = choice(physicianList)
    pos = physicianList.index(chosenPhysician)
    physicianList.pop(pos)
    cleanedUpChosenPhysician = chosenPhysician[0]
    insert(cleanedUpChosenPhysician)
    print("This case is going to", cleanedUpChosenPhysician + ".")
    select()

    increment()

    if len(physicianList) == 0:
        reset()


def reset():
    # this resets the counter the distributefairly module calls
    global count
    global physicianList
    count = 0
    c.execute("SELECT physicianName FROM physicianNamesSpecialties;")
    physicianList = c.fetchall()


def increment():
    # increments the counter of the distributefairly module
    global count
    count +=


def print_count():
    print(count)


def insert(cleanedUpPhysResult):
    # adds inputted cases into the workQueue table
    global inputCaseNumber
    global inputCaseType
    ts = time.gmtime()
    readableTs = time.strftime("%Y-%m-%d %H:%M:%S", ts)
    c2.execute("INSERT INTO workQueue (name, caseNumber, timestamp, tableCaseType) values (%s,%s,%s,%s)", (cleanedUpPhysResult, inputCaseNumber, readableTs, str(inputCaseType)))
    conn2.commit()


def select():
    # this outputs the workQueue after every addition
    sql = "SELECT * FROM workQueue"
    c2.execute(sql)

    rWQ = c2.fetchall()

    print(pd.DataFrame(rWQ, columns= ['Name','Case Number','Time Stamp','Specialty','Row ID'])) # .set_index('Row ID')


def startup():

    # create()
    global inputCaseType
    global inputCaseNumber
    inputCaseNumber = input("Enter Case Number: ")
    inputCaseType = input("Enter case type (use proper abbreviations): ")


def parse():

    global inputCaseNumber
    global inputCaseType
    caseInputsql_cmd = "SELECT specialtyRequiredToProcess,Description FROM caseTypes WHERE caseTypeName='{}'".format(inputCaseType)

    c.execute(caseInputsql_cmd)
    rows_returned = c.fetchall()
    if not rows_returned:
        print("No match to table of specimen types returned. Check the case type abbreviation and try again.")
        return
    else:
        for row in rows_returned:
            r = row[0]
            d = row[1]
            print("This is a", r, "service case. It is a", d,"type case.")

        if r != "GENERALIST":
            getPhysiciansql_cmd = "SELECT physicianName FROM physicianNamesSpecialties WHERE (specialty, specialty2, specialty3, specialty4) ='{}'".format(r)
            c.execute(getPhysiciansql_cmd)
            physResult = choice(c.fetchall())
            cleanedUpPhysResult = physResult[0]
            print("This case is going to", cleanedUpPhysResult+".")
            insert(cleanedUpPhysResult)
            select()

        else:
            distributefairly(inputCaseNumber, inputCaseType)


conn = mariaDB.connect(host='xxxxx', user='xxxxx',password='xxxxxx',db='lookupDB')
conn2 = mariaDB.connect(host='xxxxxxl', user='xxxxx',password='xxxxxxx',db='workQueue')
c = conn.cursor()
c2 = conn2.cursor()
count = 0

while True:

    startup()
    parse()

这行代码.execute(...)将失败：

getPhysiciansql_cmd = 
"SELECT physicianName FROM physicianNamesSpecialties WHERE (specialty, specialty2, specialty3, specialty4) ='{}'".format(r)
c.execute(getPhysiciansql_cmd)

我的Varr保存以下数据：

print("This is a", r, "service case. It is a", d,"type case.")

This is a THORACIC service case. It is a TRANSBRONCHIAL WANG NEEDLE ASPIRATION type case.

我的VargetPhysiciansql_cmd保存以下数据：

getPhysiciansql_cmd = SELECT physicianName FROM 

physicianNamesSpecialties WHERE (specialty, specialty2, specialty3, specialty4) ='THORACIC'

Answer 1

我想这只是我如何使用WHERE子句的问题。我在某个地方看到有多个字段的WHERE是这样写的，在字段周围使用paren。我已经尝试过将其减少到最低限度，并在WHERE子句中使用OR运算符，我已经成功地收到了正确的响应

import mysql.connector as mariaDB

conn = mariaDB.connect(host='xxxxx', user='xxxx',password='xxxxx',db='lookupDB')
conn2 = mariaDB.connect(host='xxxxx', user='xxxx',password='xxxxxx',db='workQueue')
c = conn.cursor()
c2 = conn2.cursor()

inputSpecialty = input("specialty? ")
r = inputSpecialty


c.execute("SELECT physicianName FROM physicianNamesSpecialties WHERE specialty = %s OR specialty2 = %s OR specialty3 = %s OR specialty4 = %s", (r,r,r,r))

physResult = c.fetchall()

cleanedUpPhysResult = physResult
print(cleanedUpPhysResult)

输出：

specialty? THORACIC
[('Song',), ('Han',), ('He',), ('Goldfischer',)]