Heroku PostgreSQL SQLAlchemy语法错误

Question

Exception on /viewreports [POST]
2021-06-19T01:55:40.057253+00:00 app[web.1]: Traceback (most recent call last):
packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
-packages/sqlalchemy/engine/default.py", line 608, in do_execute
2021-06-19T01:55:40.057256+00:00 app[web.1]:     cursor.execute(statement, parameters)
2021-06-19T01:55:40.057257+00:00 app[web.1]: psycopg2.errors.SyntaxError: syntax error at or near ")"
2021-06-19T01:55:40.057258+00:00 app[web.1]: LINE 1: ... ON usersport.sport_id=sport.sport_id WHERE sport_name=(?)  

2021-06-19T01:55:40.057259+00:00 app[web.1]: The above exception was the direct cause of the following exception:
2021-06-19T01:55:40.057259+00:00 app[web.1]: 
2021-06-19T01:55:40.057260+00:00 app[web.1]: Traceback (most recent call last):
2021-06-19T01:55:40.057275+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/flask/app.py", line 2447, in wsgi_app
2021-06-19T01:55:40.057275+00:00 app[web.1]:     response = self.full_dispatch_request()

2021-06-19T01:55:40.057279+00:00 app[web.1]:   File "/app/schoolsports/routes.py", line 269, in viewreports
2021-06-19T01:55:40.057285+00:00 app[web.1]:     result1=db.engine.execute("SELECT user_id FROM usersport INNER JOIN sport ON usersport.sport_id=sport.sport_id WHERE sport_name=(?)  ",(value))

2021-06-19T01:55:40.057289+00:00 app[web.1]:     self.dialect.do_execute(
2021-06-19T01:55:40.057289+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
2021-06-19T01:55:40.057290+00:00 app[web.1]:     cursor.execute(statement, parameters)
2021-06-19T01:55:40.057290+00:00 app[web.1]: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near ")"
2021-06-19T01:55:40.057290+00:00 app[web.1]: LINE 1: ... ON usersport.sport_id=sport.sport_id WHERE sport_name=(?)  
2021-06-19T01:55:40.057290+00:00 app[web.1]:                                                                     ^
2021-06-19T01:55:40.057291+00:00 app[web.1]: 
2021-06-19T01:55:40.057291+00:00 app[web.1]: [SQL: SELECT user_id FROM usersport INNER JOIN sport ON usersport.sport_id=sport.sport_id WHERE sport_name=(?)  ]
2021-06-19T01:55:40.057291+00:00 app[web.1]: [parameters: ('taekwondo',)]
2021-06-19T01:55:40.057291+00:00 app[web.1]: (Background on this error at: http://sqlalche.me/e/13/f405)
2021-06-19T01:55:40.058121+00:00 app[web.1]: 10.45.135.21 - - [19/Jun/2021:01:55:40 +0000] "POST /viewreports HTTP/1.1" 500 290 "https://mymodule.herokuapp.com/viewreports" 

我已经将我的flask web应用程序部署到Heroku，Heroku最初使用的是SQLite本地数据库，我的所有命令都可以正常工作。现在，当我部署到Heroku并使用PostgreSQL数据库时，我会遇到一些语法和编程错误，如上图所示。我没有在本地得到这些错误，现在当我部署之后，我得到了这些错误。请告知，我的代码如下：

 list2=[]
    mainlist=[]
    tables=""
    finalreportlist=[]
    excelbutton=False
    if request.method=='POST':
       
        
        sectionSelected=False
        classSelected= False
        houseselected=False
        sectionwithoutclass=False
        nostudentinlist=False
        nosport=False
            ###checking whether a a class is selected in the form. Part of form processing 
        if request.form["class1"]!='all':
            classSelected= True
                ##form data of class
            formclass=(request.form["class1"])
            ##checking whether a section is entered ###
        if request.form['section']!='all':
            sectionSelected = True
                ###form data of seciton
            formsection=(request.form["section"])

        if request.form["house"] !='all':
            houseselected= True
            formhouse=(request.form["house"])


        ##get sport form data in a loop example first badminton\
        if not request.form.getlist("sport"):
            nosport=True
        if request.form.getlist("sport") and (request.form.get("eventname")):
            flash("Select either the sport or event. Not both",'danger')
        else:
            
            for value in request.form.getlist("sport"):
                    ##getting user ids with given sport
                result1=db.engine.execute("SELECT user_id FROM usersport INNER JOIN sport ON usersport.sport_id=sport.sport_id WHERE sport_name=(?)  ",(value))
        
                    ##adding all user ids to a list
                for a in result1:
                    #print("a",a)            # for appending user ids to a list
                    list2.append(a)
                            
                                #getting all details of the user from that user table
                    query=db.engine.execute("SELECT grrno,firstname,lastname,House,Class,Section,Rollno FROM user  WHERE user.id IN(?)" ,(a))
                            
                                
                                #iterating through users
                    


                        ######query is a list of all user details. b is going though each
                    for b in query:
                        #print("User id is",b)
                        #print("User name is",b.firstname)
                            ##abc contnains each user full object with details 1 by 1
                        abc=b.items()
                            ##contains dictionary of 1 isngle user   looping     
                        dictversion=(dict(abc))
                                    #print("Selected class dictversion is",dictversion["Class"])
                        
                                            
                            ##data in the students class reocrd lika 1,2,3,4
                        classdata=dictversion["Class"]
                            ##data in students secrion record like a,b,c,d
                        sectiondata=dictversion["Section"]
                        
                                    
                            

                        if classSelected==True and sectionSelected == True :

                            if int(classdata)==int(formclass) and formsection==sectiondata:
                                mainlist.append(dictversion)
                                print("Appended in both section and class",mainlist)
                                        

                        if classSelected==True and sectionSelected==False:
                            if int(classdata)==int(formclass) :
                                    mainlist.append(dictversion)
                                    print("Class true section false")

                                        
                        if classSelected==False and sectionSelected==True:
                                
                            sectionwithoutclass=True
                            
                        if classSelected==False and sectionSelected==False:
                            mainlist.append(dictversion)
                            print("Class false section false")
                            

                        #if (sectionSelected==False and classSelected== False) and houseselected==False:
                        #    mainlist.append(dictversion)
                          #  print("All false")
 

                            
                            
                print("\n^^^^^^MAIN LIST^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^",mainlist)                 
            if houseselected==True:
                for element in mainlist:
                    print("Element=",element)
                    print("Final list before if is",finalreportlist)
                                    #print("form data is",formhouse)
                        #print("dict data is",element['House'])
                    if element['House'] ==formhouse:
                        finalreportlist.append(element)
                        print("\nFinal list after if is",finalreportlist)
                            
                                    
            else:
                print("eneterd else")
                finalreportlist=mainlist
         ```                           

Answer 1

Python DB-API连接器包定义了一个名为参数样式的值，该值表示在查询中如何表示VALUES占位符。

Sqlite使用“qmark”样式，因此值由问号表示

q = 'SELECT name FROM users WHERE id = ?'

Psycopg2使用“pyformat”样式“%s”

q = 'SELECT name FROM users WHERE id = %s'

因此，如果您将使用“qmark”样式的查询传递给心理学2执行，您会得到一个错误。

幸运的是，SQLAlChemy提供了一种独立于基础数据库连接器编写原始SQL查询的方法。通过使用“命名”参数样式，并从查询中创建文本对象，SQLAlChemy将自动为基础连接器生成正确的语句。请注意，参数值必须在字典中传递。

import sqlalchemy as sa
...
q = 'SELECT name FROM users WHERE id = :id'
result = engine.execute(sa.text(q), {'id': 42})