Re: [sqlite] Re: select date using variables in Python
On Dec 12, 2007 1:51 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> C M wrote:
> >
> > Thanks to you both--using the || did the trick, and I can try the other
> > approaches mentioned as well. In Python Igor's suggestion was just:
> >
> > amount = "+1"
> > cur.execute('SELECT string, d FROM test WHERE d >= date("now", ? || ? ||
> "
> > days")',amount)
> >
> >
> You will might be better off building the entire argument string in
> Python and then binding that string to a single argument to the date
> function in the SQL. You can then use the same query to do offsets in
> hours, days, months, or years for example. It will probably be slightly
> faster as well since SQLite will be parsing an executing a simpler SQL
> statement.
>
> You should also invert your string quoting in Python. SQL uses single
> quotes for literal values, and double quotes for identifiers (such as
> table and column names). SQLite accepts double quotes around literals as
> an extension, but it can get you into trouble if you have a column with
> the same name as your literal value. In that case it will be interpreted
> as the column name. You might even want to use Python's triple quotes to
> allow both single and double quotes to be used in the SQL string itself.
>
> Note, the plus character in '+1 days' is not needed. It is simply a sign
> character, it does not signify addition. You only need the sign if you
> want a negative offset.
>
> amount = 1
> unit = "days"
> offset = str(amount) + " " + unit
> cur.execute("SELECT string, d FROM test WHERE d >= date('now', ?)",
> offset)
>
>
> HTH
> Dennis Cote
>
Thank you--very useful to know.
Re: [sqlite] Re: select date using variables in Python
C M wrote:
Thanks to you both--using the || did the trick, and I can try the other
approaches mentioned as well. In Python Igor's suggestion was just:
amount = "+1"
cur.execute('SELECT string, d FROM test WHERE d >= date("now", ? || ? || "
days")',amount)
You will might be better off building the entire argument string in
Python and then binding that string to a single argument to the date
function in the SQL. You can then use the same query to do offsets in
hours, days, months, or years for example. It will probably be slightly
faster as well since SQLite will be parsing an executing a simpler SQL
statement.
You should also invert your string quoting in Python. SQL uses single
quotes for literal values, and double quotes for identifiers (such as
table and column names). SQLite accepts double quotes around literals as
an extension, but it can get you into trouble if you have a column with
the same name as your literal value. In that case it will be interpreted
as the column name. You might even want to use Python's triple quotes to
allow both single and double quotes to be used in the SQL string itself.
Note, the plus character in '+1 days' is not needed. It is simply a sign
character, it does not signify addition. You only need the sign if you
want a negative offset.
amount = 1
unit = "days"
offset = str(amount) + " " + unit
cur.execute("SELECT string, d FROM test WHERE d >= date('now', ?)", offset)
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: select date using variables in Python
On Dec 12, 2007 8:20 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> C M <[EMAIL PROTECTED]> wrote:
> > cur.execute('SELECT string FROM test WHERE d >= date("now","+1 day")')
> >
> > However, I'd like to make it flexible, so that a user can put in an
> > amount of days forward or backward and the query will use
> > that--basically I want the user to be able to select the date range
> > over the data in the table. I tried something like:
> >
> > amount = "1" #just to try it, later this will refer to a user-chosen
> > variable
> > cur.execute ('SELECT string FROM test WHERE d >= date("now", "+",?,"
> > day")',amount)
> >
> > But of course that's not right and it doesn't work. What is the right
> > syntax in this case to use the ? to stand for the 1 in the original
> > "+1 day" portion?
>
> I don't know Python well enough, but the SQL statement you want would
> look like this:
>
> SELECT string FROM test WHERE d >= date('now', ? || ' days');
>
> || is the string concatenation operator in SQL.
>
> Igor Tandetnik
>
Thanks to you both--using the || did the trick, and I can try the other
approaches mentioned as well. In Python Igor's suggestion was just:
amount = "+1"
cur.execute('SELECT string, d FROM test WHERE d >= date("now", ? || ? || "
days")',amount)
