Python Forum

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 row

My 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.

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?

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.

(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:

• In SQL strings are delimited by single quotes (but if you use prepared statements you won't need quotes at all excepts around constant values that are the same in all requests)
  
• LIKE requires a % or _ (that act respectively like * and ? for file name in most shells). So you want your format to be "%{:4d}{:02d}%" (or "{:4d}{:02d}%" if the strings you search begin with the year)
  

(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/6998...ith-select

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!

(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.

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!

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.

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 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. 

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

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:

• checking for an exact match between the name and  '%JohnDoe%' (because the '%' are taken literally since you are using = and not LIKE
  
• accepting any rental rate (assuming rental_rate is a string and not a number)
  
• checking only for the month in the rental date (and the way it is written it can match any two digits in the date, whether they represent the year, the month, the day, or even digits between them).
  
• taking anything for the return date
  
• checking that the rental_duration contains a 'r'  
  

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.