0

Objective

All I am trying to do is retrieve a single record from a specific table where the primary key matches. I have a feeling I'm greatly over complicating this as it seems to be a simple enough task. I have a theory that it may not know the variable value because it isn't actually pulling it from the Python code but instead trying to find a variable by the same name in the database.

EDIT: Is it possible that I need to wrap my where clause in an expression statement?

Attempted

My Python code is

def get_single_record(name_to_search):
    my_engine = super_secret_inhouse_engine_constructor("sample_data.csv")
    print("Searching for " + name_to_search)
    statement = my_engine.tables["Users"].select().where(my_engine.tables["Users"].c.Name == name_to_search)

# Print out the raw SQL so we can see what exactly it's checking for
print("You are about to run: " + str(statement))
# Print out each result (should only be one)
print("Results:")
for item in my_engine.execute(statement):
    print(item)

I tried hard coding a string in its place. I tried using like instead of where. All to the same end result.

Expected

I expect it to generate something along the lines of SELECT * FROM MyTable WHERE Name='Todd'.

Actual Result

Searching for Todd
STATEMENT: SELECT "Users"."Name",  ...
FROM "Users"
WHERE "Users"."Name" = ?

That is an actual question mark appearing my statement, not simply my own confusion. This is then followed by it printing out a collection of all the records from the table, as though it successfully matched everything.

EDIT 2: Running either my own hard coded SQL string or the generated query by Alchemy returns every record from the table. I'm beginning to think the issue may be with the engine I've set up not accepting the query.

Why I'm Confused

According to the official documentation and third party sources, I should be able to compare to hardcoded strings and then, by proxy, be able to compare to a variable.

Bugs
  • 4,491
  • 9
  • 32
  • 41
Nathan Smith
  • 683
  • 1
  • 10
  • 24
  • 2
    The question mark is a placeholder for a parameter. This is used to protect against SQL injection. The end result is the same. – univerio Jul 14 '17 at 18:09
  • 1
    Thanks for confirming. A buddy of my mine suggested the same thing, and that makes sense. It's still returning every record in the table, though. There may be a problem with my engine entirely because I've even tried passing "SELECT * FROM ..." as a plain string and still get every record back. – Nathan Smith Jul 14 '17 at 18:50
  • To wrap your code simply place the statement in parentheses and enter new lines, e.g. before the `.where()`. – krassowski Jul 18 '17 at 15:40
  • That's what I thought, but it still returned all records. I think I've narrowed it down to the class we're using in house isn't actually passing my query along to the engine. Thank you for confirming. – Nathan Smith Jul 18 '17 at 16:02
  • Please post solutions as answers not as updates to the question. You can also mark the answer as accepted which will tell others the question is solved. Thank you. – Bugs Jul 18 '17 at 16:10
  • I was going to do just that once I was able to confirm SQLAlchemy's functionality with a proper database. That edit was meant to be a placeholder and to prevent people from trying to help me when the problem turned out to be in house. – Nathan Smith Jul 18 '17 at 16:19

0 Answers0