Python Forum

Hey guys now I'm trying to do a search of a table with isbn title author and year. For now I'm just trying direct searches since I don't want to involve the wildcare special chars for the LIKE searches. But I still can't get it to work. Can someone help me figure it out?

Thanks!

@app.route("/search", methods=["GET", "POST"])
def search():
    b = db.execute("SELECT * FROM books WHERE author=:search ", {"search":search}).fetchone()
    print(b)
    return render_template("search.html")

Error:
sqlalchemy.exc.ProgrammingError

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'function'
[SQL: SELECT * FROM books WHERE author=%(search)s ]
[parameters: {'search': <function search at 0x0478B418>}]
(Background on this error at: http://sqlalche.me/e/f405)
Traceback (most recent call last)

    File "C:\Python38\Lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context

    self.dialect.do_execute(

    File "C:\Python38\Lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute

    cursor.execute(statement, parameters)

    The above exception was the direct cause of the following exception:
    File "C:\Python38\Lib\site-packages\flask\app.py", line 2464, in __call__

    return self.wsgi_app(environ, start_response)

    File "C:\Python38\Lib\site-packages\flask\app.py", line 2450, in wsgi_app

    response = self.handle_exception(e)

    File "C:\Python38\Lib\site-packages\flask\app.py", line 1867, in handle_exception

    reraise(exc_type, exc_value, tb)

    File "C:\Python38\Lib\site-packages\flask\_compat.py", line 39, in reraise

    raise value

    File "C:\Python38\Lib\site-packages\flask\app.py", line 2447, in wsgi_app

    response = self.full_dispatch_request()

    File "C:\Python38\Lib\site-packages\flask\app.py", line 1952, in full_dispatch_request

    rv = self.handle_user_exception(e)

    File "C:\Python38\Lib\site-packages\flask\app.py", line 1821, in handle_user_exception

    reraise(exc_type, exc_value, tb)

    File "C:\Python38\Lib\site-packages\flask\_compat.py", line 39, in reraise

    raise value

    File "C:\Python38\Lib\site-packages\flask\app.py", line 1950, in full_dispatch_request

    rv = self.dispatch_request()

    File "C:\Python38\Lib\site-packages\flask\app.py", line 1936, in dispatch_request

    return self.view_functions[rule.endpoint](**req.view_args)

    File "C:\school\project1\application.py", line 32, in search

    b = db.execute("SELECT * FROM books WHERE author=:search ", {"search":search}).fetchone()

    File "C:\Python38\Lib\site-packages\sqlalchemy\orm\scoping.py", line 162, in do

    return getattr(self.registry(), name)(*args, **kwargs)

    File "C:\Python38\Lib\site-packages\sqlalchemy\orm\session.py", line 1277, in execute

    return self._connection_for_bind(bind, close_with_result=True).execute(

    File "C:\Python38\Lib\site-packages\sqlalchemy\engine\base.py", line 984, in execute

    return meth(self, multiparams, params)

    File "C:\Python38\Lib\site-packages\sqlalchemy\sql\elements.py", line 293, in _execute_on_connection

    return connection._execute_clauseelement(self, multiparams, params)

    File "C:\Python38\Lib\site-packages\sqlalchemy\engine\base.py", line 1097, in _execute_clauseelement

    ret = self._execute_context(

    File "C:\Python38\Lib\site-packages\sqlalchemy\engine\base.py", line 1287, in _execute_context

    self._handle_dbapi_exception(

    File "C:\Python38\Lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _handle_dbapi_exception

    util.raise_(

    File "C:\Python38\Lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_

    raise exception

    File "C:\Python38\Lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context

    self.dialect.do_execute(

    File "C:\Python38\Lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute

    cursor.execute(statement, parameters)

    sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'function'
    [SQL: SELECT * FROM books WHERE author=%(search)s ]
    [parameters: {'search': <function search at 0x0478B418>}]
    (Background on this error at: http://sqlalche.me/e/f405)

I've never worked with sqlalchemy but the error says that you've typed something wrong on line 3. In the dictionary you passed it a key of "search" and the value as the function search. I don't see what this is supposed to do so I'm assuming you made a typo around there. If not then try referring back to some documentation or something to make sure you're properly typing everything in correctly.

I've been looking I can't find the error

What happens if you run this code?

@app.route("/search", methods=["GET", "POST"])
def search():
    searchString = 'user'
    b = db.execute("SELECT * FROM books WHERE author=:search ", {"search":searchString}).fetchone()
    print(b)
    return render_template("search.html")

OK now i'm not getting an error however it's not returning anything except "None".

Also - I would like it to print the results onto the page if possible. print() goes to the output but how do I put a message on the page easily?

Thanks

I haven't worked with Flask in a while but I believe you can pass a dictionary to the html file with the info which you can use to display it there on the page. I'd suggest following a Flask tutorial or reading through the documentation to get an understanding of it.

I've read a lot and I can't get it to work. The terminology is too complex for me it doesn't help.

Do u know why it's returning "None"?

I changed the code to what's below to reflect the LIKE search. It's not working and still returning as None.

@app.route("/search", methods=["GET", "POST"])
def search():
    searchString = 'user'
    b = db.execute("SELECT * FROM books WHERE author LIKE :search'%'", {"search":searchString}).fetchone()
    print(b)
    return render_template("search.html")

(Jun-14-2020, 02:01 AM)card51shor Wrote: [ -> ]Do u know why it's returning "None"?

I assume that means that it does not find anything from the books table. I've only ever worked with sqlite3 before so I can't help you with what you're doing wrong, but I found a documentation here which seems very simple to understand if you read through it and try to understand what's being said.

sorry i have to use raw SQL with sqlalchemy i can't use the ORM. I have to use the db.execute method.

Also - I've studied the code I just cant' get it to work I need someone to set me straight.

---

@app.route("/search", methods=["GET", "POST"])
def search():
    searchString = 'user'
    b = db.execute("SELECT * FROM books WHERE author LIKE '%':search'%'", {"search":searchString}).fetchone()
    print(b)
    return render_template("search.html")

This is my current code i'm working with - still not getting any errors - but it's just coming up as None. Any suggestions? Thanks guys!

I was looking around and I think the line of code should work if it's typed out like this.

@app.route("/search", methods=["GET", "POST"])
def search():
    searchString = 'user' #Change this to represent an actual author that's stored in the database
    b = db.execute(f"SELECT * FROM books WHERE author LIKE '%{searchString}%'").fetchone()
    print(b) #See if this prints an actual result
    return render_template("search.html", anyVariableName=b) #This will pass b to the html file with whatever name you provide where it says "anyVariableName". You can use access the variable in the html file via {{ variableName }} where "variableName" is the name that you passed as an argument

Hopefully this helps.