Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > MySQLdb integer question

Reply
Thread Tools

MySQLdb integer question

 
 
Ansgar Wollnik
Guest
Posts: n/a
 
      10-15-2003
Hello,

I use Python with MySQLdb to transfer data from one database to another.
When I SELECT the data from the first table, numbers are provided with
an 'L' at the end to show, they are treated as a Long Integer (see:
http://www.esrf.fr/computing/bliss/p...-3.html#ss3.4).

Now I want to put that data into the new database but the 'L's are still
there. What can I do?

Ansgar

 
Reply With Quote
 
 
 
 
bromden
Guest
Posts: n/a
 
      10-16-2003
L indicates that this is a long integer, it behaves as normal integer
so you don't need to worry about that, use int() if it annoys you,

>>> i = 1410L
>>> i

1410L
>>> print i

1410
>>> int(i)

1410
>>> 'insert into a (b) values (%s)' % i

'insert into a (b) values (1410)'

--
bromden[at]gazeta.pl

 
Reply With Quote
 
 
 
 
Polerio Babao Jr.II
Guest
Posts: n/a
 
      10-16-2003
Ansgar Wollnik <(E-Mail Removed)> wrote in message news:<bmkghg$nfpua$(E-Mail Removed)-berlin.de>...
> Hello,
>
> I use Python with MySQLdb to transfer data from one database to another.
> When I SELECT the data from the first table, numbers are provided with
> an 'L' at the end to show, they are treated as a Long Integer (see:
> http://www.esrf.fr/computing/bliss/p...-3.html#ss3.4).
>
> Now I want to put that data into the new database but the 'L's are still
> there. What can I do?
>
> Ansgar



in order to convert it, you should use the python built-in function, int()

int() - this will convert a number with L into an interger, i.e.

>>>a = 200L
>>>a = int(a)
>>>print a

200

example taken from a tuple

>>>c = ('a',200L)
>>>print int(c[1])

200

mabuhay!
 
Reply With Quote
 
Ansgar Wollnik
Guest
Posts: n/a
 
      10-19-2003
Polerio Babao Jr.II wrote:
>>I use Python with MySQLdb to transfer data from one database to another.
>>When I SELECT the data from the first table, numbers are provided with
>>an 'L' at the end to show, they are treated as a Long Integer (see:
>>http://www.esrf.fr/computing/bliss/p...-3.html#ss3.4).
>>
>>Now I want to put that data into the new database but the 'L's are still
>>there. What can I do?


>>>>c = ('a',200L)
>>>>print int(c[1])


OK, that would work, if I would know what field contains an Integer or
not...

Could you help to determine weather a value is an Integer or not so I
can to the needed INSERT INTO syntax?

Ansgar

 
Reply With Quote
 
Ansgar Wollnik
Guest
Posts: n/a
 
      10-19-2003
Scenario:

# myvalues
(1L,'text')

# sql="INSERT INTO %s (%s) VALUES %s"% (db,",".join(myfields),myvalues)

This doesn't work, because the Integer-Value (1L) is not converted into
a Integer (without 'L'), so the INSERT statement is:

INSERT INTO db (id, sometext) VALUES (1L,'text')
^^




What can I do?

Ansgar

 
Reply With Quote
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      10-19-2003
Ansgar Wollnik fed this fish to the penguins on Sunday 19 October 2003
12:27 pm:

>
>
> Scenario:
>
> # myvalues
> (1L,'text')
>
> # sql="INSERT INTO %s (%s) VALUES %s"%
> # (db,",".join(myfields),myvalues)


(forgive the KNode unintelligent attempt at line wrapping)


I believe the recommendation is to NOT build the full SQL external to
the db call itself. Instead, let the call handle the data conversion...

For your example, that would probably be something on the lines of:

sql = "insert into %s (%s) values (%s)" % (db, ",".join(myfields),
",".join(["%s"] * len(myvalues)) )
# build core SQL with appropriate number of %s for values

cursor.execute(sql, myvalues)
# let MySQLdb do the type conversion as possible

Though I suspect anything that Python sees as a LONG may be too large
to fit any MySQL data field too... If you're storing it in a
string/text field, why is the L a problem?

--
> ================================================== ============ <
> http://www.velocityreviews.com/forums/(E-Mail Removed) | Wulfraed Dennis Lee Bieber KD6MOG <
> (E-Mail Removed) | Bestiaria Support Staff <
> ================================================== ============ <
> Bestiaria Home Page: http://www.beastie.dm.net/ <
> Home Page: http://www.dm.net/~wulfraed/ <


 
Reply With Quote
 
Skip Montanaro
Guest
Posts: n/a
 
      10-19-2003

>>> Now I want to put that data into the new database but the 'L's are
>>> still there. What can I do?


>>>>> c = ('a',200L)
>>>>> print int(c[1])


Ansgar> OK, that would work, if I would know what field contains an
Ansgar> Integer or not...

Ansgar,

It's not clear to me why you're having a problem with this. In the version
of MySQLdb I use (0.9.2), longs are passed through
MySQLdb.converters.Thing2Str before being folded into an SQL statement:

def Thing2Str(s, d):
"""Convert something into a string via str()."""
return str(s)

If s is a long, str(s) returns a string containing only digits in Python
2.1, 2.2 and 2.3.

Nonetheless, if things aren't working for you, you can add a custom
converter to your MySQLdb connection which maps longs to ints. Try
something like this:

def Long2Str(s, d):
return str(int(s))

import MySQLdb
import MySQLdb.converters
converter = MySQLdb.converters.conversions
converter[long] = Long2Str

conn = MySQLdb.Connection(..., conv=converter)

If you need help straight from the horse's mouth (Andy Dustman), your best
bet is to post a question to the help forum on the mysql-python project
website on SourceForge: <http://sf.net/projects/mysql-python>.

Skip

 
Reply With Quote
 
Skip Montanaro
Guest
Posts: n/a
 
      10-19-2003

Ansgar> # sql="INSERT INTO %s (%s) VALUES %s"% (db,",".join(myfields),myvalues)

Then don't do it that way. Try this instead:

conn = MySQLdb.Connection(...)
curs = conn.cursor()
stmt = "INSERT INTO %s (%s) VALUES (%s)" % (db, ",".join(myfields),
",".join(['%s']*len(myfields)))
curs.execute(stmt, tuple(myvalues))

The assignment to stmt results in another format string with the table name
and field names filled in and the right number of %s placeholders. You then
pass your values as a tuple to the cursor's execute() function and let
MySQLdb perform the necessary escape trickery on the values.

Skip

 
Reply With Quote
 
Polerio Babao Jr.II
Guest
Posts: n/a
 
      10-20-2003
Ansgar Wollnik <(E-Mail Removed)> wrote in message news:<bmung0$qbj7e$(E-Mail Removed)-berlin.de>...
> Polerio Babao Jr.II wrote:
> >>I use Python with MySQLdb to transfer data from one database to another.
> >>When I SELECT the data from the first table, numbers are provided with
> >>an 'L' at the end to show, they are treated as a Long Integer (see:
> >>http://www.esrf.fr/computing/bliss/p...-3.html#ss3.4).
> >>
> >>Now I want to put that data into the new database but the 'L's are still
> >>there. What can I do?

>
> >>>>c = ('a',200L)
> >>>>print int(c[1])

>
> OK, that would work, if I would know what field contains an Integer or
> not...
>
> Could you help to determine weather a value is an Integer or not so I
> can to the needed INSERT INTO syntax?
>
> Ansgar



Use type() function to get the data type of your values.

>>> a = '23'
>>> b = 21L
>>> c = 22
>>> d = 23.45
>>> print type(a), type(b), type(c), type(d)

<type 'str'> <type 'long'> <type 'int'> <type 'float'>

>>> a = '23'
>>> b = type(a)
>>> c = '%s' % b
>>> if c[7:10]=='str':

.... print 'This is a string!'
....
This is a string!
>>> a = '%s' % type(200.45)
>>> a

"<type 'float'>"
>>> a[7:11]

'floa'
>>> a[7:12]

'float'
>>> if a[7:12]=='float':

.... print 'This is a float number!'
....
This is a float number!
>>>


Mabuhay!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I add an Integer to another Integer? Sebastian Stelzer Java 6 04-07-2010 07:03 PM
CType(x,Integer) vs. Integer.Parse(x) =?Utf-8?B?Sm9l?= ASP .Net 7 02-07-2006 02:30 AM
how do I make Class.forName("Integer") returning java.lang.Integer? Johannes Zellner Java 22 12-19-2005 11:22 AM
How do I add an Integer to another Integer? Sebastian Stelzer Java 2 10-15-2004 01:17 PM
No Math.min(Integer, Integer)? =?ISO-8859-1?Q?Thomas_Gagn=E9?= Java 0 07-29-2003 07:46 PM



Advertisments