Mailing List Archive: Python: Python

Date/Time conversions

Index | Next | Previous | View Threaded

r.albanese at qut

Apr 19, 1999, 7:51 PM

Post #1 of 4 (192 views)
Permalink

Date/Time conversions

--------------FA78C71110B61F82D85316D0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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

--------------FA78C71110B61F82D85316D0
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
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
  </html>

--------------FA78C71110B61F82D85316D0--

rhww at erols

Apr 19, 1999, 9:12 PM

Post #2 of 4 (191 views)
Permalink

Date/Time conversions [In reply to]

Rico Albanese wrote:
> 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` + ","

duncan at rcp

Apr 20, 1999, 1:42 AM

Post #3 of 4 (190 views)
Permalink

Date/Time conversions [In reply to]

Rico Albanese <r.albanese [at] qut> wrote in
<371BEBA7.5DBC1B0F [at] qut>:

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

--
Duncan Booth duncan [at] dales
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

mal at lemburg

Apr 21, 1999, 1:31 AM

Post #4 of 4 (190 views)
Permalink

Date/Time conversions [In reply to]

Rico Albanese wrote:
>
> 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/ :
---------------------------------------------------------

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads