MySQLdb integer question

Discussion in 'Python' started by Ansgar Wollnik, Oct 15, 2003.

  1. 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
    Ansgar Wollnik, Oct 15, 2003
    #1
    1. Advertising

  2. Ansgar Wollnik

    bromden Guest

    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
    bromden, Oct 16, 2003
    #2
    1. Advertising

  3. Ansgar Wollnik <> wrote in message news:<bmkghg$nfpua$-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/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.

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

    200

    example taken from a tuple

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

    200

    mabuhay!
    Polerio Babao Jr.II, Oct 16, 2003
    #3
  4. 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/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
    Ansgar Wollnik, Oct 19, 2003
    #4
  5. 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
    Ansgar Wollnik, Oct 19, 2003
    #5
  6. 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?

    --
    > ============================================================== <
    > | Wulfraed Dennis Lee Bieber KD6MOG <
    > | Bestiaria Support Staff <
    > ============================================================== <
    > Bestiaria Home Page: http://www.beastie.dm.net/ <
    > Home Page: http://www.dm.net/~wulfraed/ <
    Dennis Lee Bieber, Oct 19, 2003
    #6

  7. >>> 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
    Skip Montanaro, Oct 19, 2003
    #7
  8. 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
    Skip Montanaro, Oct 19, 2003
    #8
  9. Ansgar Wollnik <> wrote in message news:<bmung0$qbj7e$-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/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)

    <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!
    Polerio Babao Jr.II, Oct 20, 2003
    #9
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?Sm9l?=

    CType(x,Integer) vs. Integer.Parse(x)

    =?Utf-8?B?Sm9l?=, Feb 6, 2006, in forum: ASP .Net
    Replies:
    7
    Views:
    5,948
    =?Utf-8?B?RGF2aWQgQW50b24=?=
    Feb 7, 2006
  2. =?ISO-8859-1?Q?Thomas_Gagn=E9?=

    No Math.min(Integer, Integer)?

    =?ISO-8859-1?Q?Thomas_Gagn=E9?=, Jul 29, 2003, in forum: Java
    Replies:
    0
    Views:
    502
    =?ISO-8859-1?Q?Thomas_Gagn=E9?=
    Jul 29, 2003
  3. Sebastian Stelzer

    How do I add an Integer to another Integer?

    Sebastian Stelzer, Oct 14, 2004, in forum: Java
    Replies:
    2
    Views:
    490
    Yu SONG
    Oct 15, 2004
  4. Sebastian Stelzer

    How do I add an Integer to another Integer?

    Sebastian Stelzer, Oct 14, 2004, in forum: Java
    Replies:
    6
    Views:
    45,403
    JavaBean2010
    Apr 7, 2010
  5. cjl
    Replies:
    3
    Views:
    987
    John Nagle
    May 21, 2007
Loading...

Share This Page