Problems creating a search engine

[Yoriz]

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()


class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    author = Column(String)

    def __repr__(self):
        return f'<Book(name= {self.name}, author= {self.author})>'


Base.metadata.create_all(engine)

book = Book(name='Coding', author='Yoriz')
session.add(book)
book2 = Book(name='More Coding', author='some one')
session.add(book2)
session.commit()

print(session.query(Book).all())

search_string = '%or%' # % before and after match anything

print(session.query(Book).filter(Book.author.like(search_string)).one())

print(session.execute('SELECT * FROM book WHERE author LIKE :author',
                      {'author': search_string}).fetchone())

Output:
[<Book(name= Coding, author= Yoriz)>, <Book(name= More Coding, author= some one)>]
<Book(name= Coding, author= Yoriz)>
(1, 'Coding', 'Yoriz')

[card51shor]

really appreciate it man I will come home and test it tonight after work.

Thank you!!!

[card51shor]

Hey so I'm trying to apply the code to my project and it's just not working. I just don't understand how to insert the SQL. why is it so complicated?

Where can I see where I can just learn how to type the SQL code? I don't get it. It shouldn't be this complicated. It's easier to write actual SQL code on its own. Why would Python make it more complicated? With all these placeholders and secret curly brackets and all this?

Your example is not taking user input so it doesn't really help me as far as syntax goes.

Thanks so much for taking the time, though.

Can someone please help me try to figure this out? I've been working on this project for 6 weeks and I just keep hitting a wall and I can't find solid documentation to back me up.

Please don't just send me the link to the documentation - it isn't specific and it's very confusing.

thank you!

Here is my code currently :

@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")

[SheeppOSU]

It looks like your code should work fine, run the code below and see what it prints.

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

If it returns nothing then that means that their is no data stored in the table which would explain why it returns none when you search through the table.

[ndc85430]

Perhaps using SQLAlchemy is just adding complexity you don't really need right now? I don't know why you'd start off with that rather than using the database library (i.e. Psycopg for PostgreSQL) directly. The placeholders are necessary to allow queries to be parameterised in a way that isn't vulnerable to SQL injection.

It's very difficult to diagnose the problems here because we can't reproduce them. This is probably beyond you right now, but the way we manage this in professional software development is to automate the testing where possible. You'd have a class that abstracts away the database access and one of the test cases for it would be something like "when I insert some items and I ask for a particular one, then that item is retrieved". Of course you'd need to have steps to make sure the database is in the right state (e.g. the tables are created, etc.), but that's also quite possible.

---

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

Line 4 looks like it uses a regular Python f-string. You really shouldn't be doing that as it's vulnerable to SQL injection. Let the database library handle the interpolation.

[card51shor]

Hey guys I figured it out. My code works. However, I have another question that I posted if you can take a look at it. Here's the fixed code:

@app.route("/search", methods=["GET", "POST"])
def search():
    search = request.form.get("search")
    search1 = search + '%'
    b = db.execute("SELECT * FROM books WHERE author LIKE :search OR isbn LIKE :search OR title LIKE :search LIMIT 10", {'search': search1}).fetchall()
    print(b)
    return render_template("search.html")
            

[Yoriz]

Your example is not taking user input so it doesn't really help me as far as syntax goes.

Then add user input to it , try things for yourself !!!

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()


class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    author = Column(String)

    def __repr__(self):
        return f'<Book(name= {self.name}, author= {self.author})>'


Base.metadata.create_all(engine)

book = Book(name='Coding', author='Yoriz')
session.add(book)
book2 = Book(name='More Coding', author='some one')
session.add(book2)
session.commit()

print(session.query(Book).all())

# search_string = '%or%' # % before and after match anything
search_string = input('Input the search string (note wild cards are added here )')

print(session.query(Book).filter(Book.author.like(search_string)).one())

print(session.execute('SELECT * FROM book WHERE author LIKE :author',
                      {'author': search_string}).fetchone())

if you only enter 'or'
you will get the error

Error:
sqlalchemy.orm.exc.NoResultFound: No row was found for one()

using wild cards will find a result

Output:
<Book(name= Coding, author= Yoriz)>
(1, 'Coding', 'Yoriz')

Please don't just send me the link to the documentation - it isn't specific and it's very confusing.

Learn from documentation !!!
SQL LIKE Operator

Where can I see where I can just learn how to type the SQL code? I don't get it. It shouldn't be this complicated. It's easier to write actual SQL code on its own. Why would Python make it more complicated? With all these placeholders and secret curly brackets and all this?

Sqlalchemy makes it easier why not leverage it go through the tutorial
Object Relational Tutorial

Here is my code currently :

@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")

Are you making use of wildcards if you are not adding them nothing will be found if you are searching for a part of a sting
what happens if you add the wildcards in like below

@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")

if you want to add the wild cards automatically, add them in your method

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