Date/Time conversions 
Author Message
 Date/Time conversions

I am writing a web based database application and I am having problems
in creating a string that contains an integer in it.   I wish to store
the login time as an integer so that I may do some time difference
calculations based  on the stored login time (ie. the integer).  The SQL
statement string creation fails when I have the integer variable
"logtime" in it but not when it is removed.  I believe that my problem
is caused by trying to insert an integer into a string.  The database
works fine (ie I can add integers to the column "LogTime").

This is part of the code:
.
import dbi,odbc  #For ODBC stuff
import time  #For ODBC and time related stuff
import rexec  #For executing the SQL query
import cgi  #For processing the form stuff
import re  #For regular expression and pattern matching

#-------------------------------------------------------------------------------------------------------

form = cgi.SvFormContentDict()
.
.
.
Caller=Ph=Loc=Sch=JbDes=JbPr=None
Caller=form['Name']
Ph=form['PhNumb']
Loc=form['Locn']
Sch=form['Schl']
JbDes=form['JobDesc']
JbPr=form['urgency']
.
.
logtm=time.time()    #a floating point
logtime=int(logtm)    #an integer
.
.
frontpar='('
backpar=')'

#Build the SQL statement string

thesql="INSERT INTO CSJobs.compjobs
(LogTime,Caller,Phone_Extension,Caller_Category,Location,Job_Description,Job_Priority,LogNumb,Log_Date,Finished)
VALUES "
thesql=thesql + frontpar + logtime + ","
The inclusion of this variable ^ (ie logtime) causes this script to
fail.  When it is removed, it works ok.

thesql= thesql + "'" + Caller + "'" + ","
thesql=thesql + "'" + Ph + "'" + "," + "'" + Sch + "'" + ","
thesql=thesql + "'" + Loc + "'" + "," + "'" + JbDes + "'"
thesql=thesql + "," + "'" + JbPr + "'" + "," + "'" + LogN + "'" + "," +
"'" + logdate + "'" + "," + "'" + "n" + "'"
thesql=thesql + backpar
thesql='"""' + thesql + '"""'
thesql=frontpar + thesql + backpar
thecode='crsr.execute' + thesql
.
.
exec(thecode)

Any suggestions as to what I am doing wrong??

Thanks in advance for any assistance.

Rico



Sat, 06 Oct 2001 03:00:00 GMT  
 Date/Time conversions

Quote:

> thesql=thesql + frontpar + logtime + ","
> The inclusion of this variable ^ (ie logtime) causes this script to fail.  
> When it is removed, it works ok.

Use backquotes to turn the integer into a string, i.e.,

        thesql=thesql + frontpar + `logtime` + ","



Sat, 06 Oct 2001 03:00:00 GMT  
 Date/Time conversions


Quote:
>I am writing a web based database application and I am having problems
>in creating a string that contains an integer in it.   I wish to store
>the login time as an integer so that I may do some time difference
>calculations based  on the stored login time (ie. the integer).  The SQL
>statement string creation fails when I have the integer variable
>"logtime" in it but not when it is removed.  I believe that my problem
>is caused by trying to insert an integer into a string.  The database
>works fine (ie I can add integers to the column "LogTime").

Your problem is that you are trying to concatenate a string with an
integer. The arguments to the '+' operator should both be numbers, or both
strings, you cannot mix strings and numbers here.

There are two obvious solutions. One is to convert logtime to a string.
e.g.
thesql=thesql + frontpar + str(logtime) + ","

The other, possibly better one is to build up your SQL string using the '%'
operator. For example, your SQL statement may be build up something like
this:

SQLtemplate = '''INSERT INTO CSJobs.compjobs
(LogTime,Caller,Phone_Extension,Caller_Category,Location,Job_Description,Jo
b_Priority,LogNumb,Log_Date,Finished)
VALUES (%(logtime)s, '%(Caller)s', '%(Ph)s', '%(Sch)s', '%(Loc)s',
'%(JbDes)s', '%(JbPr)s', '%(LogN)s', '%(logdate)s', 'n')
'''
thesql = SQLtemplate % locals()
crsr.execute(thesql)

Which does all the neccessary conversions to strings as you go.

--

int month(char *p){return(124864/((p[0]+p[1]-p[2]&0x1f)+1)%12)["\5\x8\3"
"\6\7\xb\1\x9\xa\2\0\4"];} // Who said my code was obscure?
http://dales.rmplc.co.uk/Duncan



Sat, 06 Oct 2001 03:00:00 GMT  
 Date/Time conversions

Quote:

> I am writing a web based database application and I am having problems
> in creating a string that contains an integer in it.   I wish to store
> the login time as an integer so that I may do some time difference
> calculations based  on the stored login time (ie. the integer).  The
> SQL statement string creation fails when I have the integer variable
> "logtime" in it but not when it is removed.  I believe that my problem
> is caused by trying to insert an integer into a string.  The database
> works fine (ie I can add integers to the column "LogTime").

Note that you should *bind* variables to parameters in the
SQL statement rather than insert them literaly into the statement
itself, e.g.

c.execute('insert into table values (?,?)',(123,'string'))

rather than use

c.execute('insert into table values (123,"string")')

See the database topic guide on www.python.org for more details.

--
Marc-Andre Lemburg                               Y2000: 254 days left
---------------------------------------------------------------------
          : python Pages >>> http://starship.skyport.net/~lemburg/  :
           ---------------------------------------------------------



Sun, 07 Oct 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. date/time conversion

2. string to date/time conversion

3. Date/Time Conversion

4. Create Time / date or Modified Time / date of a txt file

5. convert labview date/time to excel date/time

6. DOS compressed date/time into real date/time

7. Date-Time/TOD clock conversion (was Re: (no subject given))

8. Date & Time Conversions on VM

9. Conversion of date / current time to DateAndTime

10. Date Conversion problem: Date == 2079/12/31

11. Clarion Date (Long) -> Access Date\Time

12. time to time with different dates...

 

 
Powered by phpBB® Forum Software