format String in SQL statement. - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Homework (https://python-forum.io/forum-9.html) +--- Thread: format String in SQL statement. (/thread-3469.html) |
format String in SQL statement. - Steven - May-26-2017 Hello everyone. I am having a problem formatting a string. That's what it boils down to. I am using a sglite3 database and looking for a date format in the database. I'll show the code here: def film_list(name, month, year): # YOUR CODE HERE d = "{:4d}{:02d}".format(year,month) query = 'select title, rental_rate, rental_date, return_date, rental_duration\ FROM film JOIN inventory using(film_id)\ JOIN rental using(inventory_id)\ JOIN customer using(customer_id)\ WHERE last_name = "'+ name + '" AND WHERE rental_date LIKE" "{:4d}{:02d}".format(year,month)' print(query) for row in db.execute(query): print(row) return rowMy query prints out like this: select title, rental_rate, rental_date, return_date, rental_duration FROM film JOIN inventory using(film_id) JOIN rental using(inventory_id) JOIN customer using(customer_id) WHERE last_name = "Ebert" AND WHERE rental_date LIKE" "{:4d}{:02d}".format(year,month)What I'm sending to the function is this: leo = film_list('Ebert', 6, 2005)I need it to be formatted like this: assert leo[0] == ('Lonely Elephant', 2.99, '2005-06-16 20:59:35.000', '2005-06-24 15:31:35.000', 3)Everything will should work if I can format the date to look like the above string '2005-06-16 20:59:35.000'. Thanks folks. I've been trying everything I can, but I cannot get the correct quote placement, I think. RE: format String in SQL statement. - Ofnuts - May-26-2017 You rental date is a true string in the DB and not a time stamp? Have you even been told that you shouldn't format your parameters in your queries but should be using prepared statements and bound variables? RE: format String in SQL statement. - Steven - May-26-2017 No it's a string I'm supposed to check for. I have not been told I shouldn't format my parameters in my queries, but it's fairly open. The point is to hack our python code that implements sql statements to do specefic things. I cannot figure out a way to format my data so that it matches the assertion. I'm not sure what you mean by formatting my parameters, but I assume you mean, put my query into a variable and and then add my parameters to it somehow. Thanks for your reply and any help. I'm really stuck on this simple thing. RE: format String in SQL statement. - Ofnuts - May-27-2017 (May-26-2017, 11:12 PM)Steven Wrote: No it's a string I'm supposed to check for. I have not been told I shouldn't format my parameters in my queries, but it's fairly open. The point is to hack our python code that implements sql statements to do specefic things. I cannot figure out a way to format my data so that it matches the assertion. Your formating works (I assume you give a full year, such as 2017 and want a left-padded month, like 201705 ).However, you have other problems:
(May-26-2017, 11:12 PM)Steven Wrote: I'm not sure what you mean by formatting my parameters, but I assume you mean, put my query into a variable and and then add my parameters to it somehow. Yes. See here: https://stackoverflow.com/questions/6998968/using-sqlite-prepared-statements-with-select RE: format String in SQL statement. - Steven - May-27-2017 That worked. I now have it working using your suggestions. Here's the updated code so other people may be helped. query = 'select title, rental_rate, rental_date, return_date, rental_duration\ FROM film JOIN inventory using(film_id)\ JOIN rental using(inventory_id)\ JOIN customer using(customer_id)\ WHERE last_name = ? AND rental_date LIKE ?' print('Query',query) for row in db.execute(query,['%name%','%month%']): print(row) return row leo = film_list('Ebert', 6, 2005)Now onto throwing it into a list in the for loop! RE: format String in SQL statement. - Ofnuts - May-27-2017 (May-27-2017, 06:16 PM)Steven Wrote: That worked. I now have it working using your suggestions. Here's the updated code so other people may be helped.Normally not such a good idea. In the general case, you can be dragging in memory the whole DB contents. Typically you use a cursor and get the elements one by one with the cursor. For extreme pythonicity, you hide your cursor behind a generator, but you aren't there yet. format String in SQL statement. More problems. - Steven - May-29-2017 I cannot seem to return the correctly formatted information. I have provided the assertions that are being checked to be more clear. The information requested is in the, but I cannot figure out how to get it and return it properly. I have written so many different things and cannot seem to do it right. Here's my code. The parameters are: name is the last name, month is the month it was rented, year is the year. In the database, the month and year look like this: '2005-06-16 20:59:35.000' and are an actual string. I am trying to return the title, the rental rate, date rented, and the duration (which is an integer). Just like it is shown in the second assertion. Thanks for any help. def film_list(name, month, year): query = 'select title, rental_rate, rental_date, return_date, rental_duration\ FROM film JOIN inventory using(film_id)\ JOIN rental using(inventory_id)\ JOIN customer using(customer_id)\ WHERE last_name = ? AND rental_rate LIKE ? AND rental_date LIKE ? AND return_date LIKE ? AND rental_duration LIKE ?' test = db.execute(query,['%'+name+'%','%%','%'+month+'%','%%','%r%']) rows = [] for row in db.execute(query, test): rows.append(row) print(rows) return rows leo = film_list('Ebert', 6, 2005) assert len(leo) == 5 assert leo[0] == ('Lonely Elephant', 2.99, '2005-06-16 20:59:35.000', '2005-06-24 15:31:35.000', 3) Assertion Error: Getting values from SQL statement, Continued - Steven - May-29-2017 I cannot seem to return the correctly formatted information. I have provided the assertions that are being checked to be more clear. The information requested is in the, but I cannot figure out how to get it and return it properly. I have written so many different things and cannot seem to do it right. Here's my code. The parameters are: name is the last name, month is the month it was rented, year is the year. In the database, the month and year look like this: '2005-06-16 20:59:35.000' and are an actual string. I am trying to return the title, the rental rate, date rented, and the duration (which is an integer). Just like it is shown in the second assertion. Thanks for any help. def film_list(name, month, year): query = 'select title, rental_rate, rental_date, return_date, rental_duration\ FROM film JOIN inventory using(film_id)\ JOIN rental using(inventory_id)\ JOIN customer using(customer_id)\ WHERE last_name = ? AND rental_rate LIKE ? AND rental_date LIKE ? AND return_date LIKE ? AND rental_duration LIKE ?' test = db.execute(query,['%'+name+'%','%%','%'+month+'%','%%','%r%']) rows = [] for row in db.execute(query, test): rows.append(row) print(rows) return rows leo = film_list('Ebert', 6, 2005) assert len(leo) == 5 assert leo[0] == ('Lonely Elephant', 2.99, '2005-06-16 20:59:35.000', '2005-06-24 15:31:35.000', 3)I am getting these errors when I run this: ProgrammingError Traceback (most recent call last) <ipython-input-52-329e41a4116e> in <module>() 11 return rows 12 ---> 13 leo = film_list('Ebert', 6, 2005) 14 assert len(leo) == 5 15 assert leo[0] == ('Lonely Elephant', 2.99, '2005-06-16 20:59:35.000', '2005-06-24 15:31:35.000', 3) <ipython-input-52-329e41a4116e> in film_list(name, month, year) 1 def film_list(name, month, year): 2 ----> 3 querytest = db.execute('select title, rental_rate, rental_date, return_date, rental_duration FROM film JOIN inventory using(film_id) JOIN rental using(inventory_id) JOIN customer using(customer_id) WHERE last_name = ? AND rental_rate LIKE ? AND rental_date LIKE ? AND return_date LIKE ? AND rental_duration LIKE ?') 4 5 test = ['%'+name+'%','','%'+month+'%','',''] ProgrammingError: Incorrect number of bindings supplied. The current statement uses 5, and there are 0 supplied. RE: format String in SQL statement. - buran - May-29-2017 Just merged the new thread into your old one. It's still the same SQL statement problem, so please keep the discussion in the original thread RE: format String in SQL statement. More problems. - Ofnuts - May-29-2017 With this: WHERE last_name = ? AND rental_rate LIKE ? AND rental_date LIKE ? AND return_date LIKE ? AND rental_duration LIKE ?' test = db.execute(query,['%'+name+'%','%%','%'+month+'%','%%','%r%'])you are in effect:
Make it simple for yourself, just make queries that take only one single criterion (name, rate,dates, duration...), and test them individually. Once you have them all working combine them in one single query. In addition, people rarely come up with SQL queries directly in their application code. They use SQL editors, that allow them to test their queries directly, show them error in the SQL synatx, display results, etc... . Once these queries work, they are adapted to be part of the code of the application (which at that point is a fairly trivial task). A free SQL editor is Squirrel-SQL. |