MySQLdb integer question

B

bromden

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,
'insert into a (b) values (1410)'
 
P

Polerio Babao Jr.II

Ansgar Wollnik said:
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/python2/MySQL/MySQLdb-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.
200

example taken from a tuple
200

mabuhay!
 
A

Ansgar Wollnik

Polerio said:
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/python2/MySQL/MySQLdb-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
 
A

Ansgar Wollnik

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
 
D

Dennis Lee Bieber

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?

--
 
S

Skip Montanaro

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

Skip Montanaro

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
 
P

Polerio Babao Jr.II

Ansgar Wollnik said:
Polerio said:
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/python2/MySQL/MySQLdb-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)
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
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top