FAQ
Hi all,




   I'm new to python, i am connecting mysql database with python. now i want
to do sanitation on the database, like to remove "\n", extra spaces blah
blah.. and update it back to mysql database. i was trying somthing, here is
my code, can you please provide me solution for this..


#!/usr/bin/python
import MySQLdb as mdb


con = mdb.connect('localhost', 'root', 'pass at 123', 'workbench');


with con:
   cur = con.cursor()
   cur.execute("SELECT descrip FROM table LIMIT 1")
   rows = cur.fetchall()
   for row in rows:
     row_new = row[0].split("\n", " ")
     row = "".join(row_new)
   print row
exit(0)
--


Regards
Boffin
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20130917/5adeeeba/attachment-0001.html>

Search Discussions

  • Ben Finney at Sep 17, 2013 at 7:35 am

    Venkat Addala <venkat.boffin@gmail.com> writes:


    I'm new to python

    Welcome! Congratulations on choosing Python for programming.

    i am connecting mysql database with python. now i want to do
    sanitation on the database, like to remove "\n", extra spaces blah
    blah.. and update it back to mysql database.


    i was trying somthing, here is my code, can you please provide me
    solution for this..

    Thank you for providing a small, complete example.


    You should also describe what behaviour you expect, and what behaviour
    you're getting instead. What happens, and how are you expecting it to be
    different?

    #!/usr/bin/python
    import MySQLdb as mdb

    con = mdb.connect('localhost', 'root', 'pass at 123', 'workbench');

    There's no need to end Python statements with a semicolon; it's only
    confusing to do it.

    rows = cur.fetchall()
    for row in rows:

    You never use ?rows? for anything else, so you may as well forget it and
    just iterate directly over the return value::


         for row in cur.fetchall():

    row_new = row[0].split("\n", " ")

    Read the documentation for ?str.split? to see what is wrong with the
    above call.




    You might also be interested in the ?python-tutor? forum, specially
    designed for beginners with basic questions about Python
    <URL:http://mail.python.org/mailman/listinfo/tutor>
    <URL:http://dir.gmane.org/gmane.comp.python.tutor>.


    Good hunting to you!


    --
      \ ?Sunday: A day given over by Americans to wishing that they |
       `\ themselves were dead and in Heaven, and that their neighbors |
    _o__) were dead and in Hell.? ?Henry L. Mencken |
    Ben Finney
  • Venkat Addala at Sep 17, 2013 at 8:27 am
    hey Ben,


    Thanks for your solution, i am done with this before, now i'm having
    problem after deleting \n and extra spaces, i want to update it back it
    again to mysql db.


    For this i tried using regular expression check this code;


    # -*- coding: utf-8 -*-
    import re
    import MySQLdb


    pattern = re.compile('@(.*)@.*$')


    conn = MySQLdb.connect(
       host='localhost', user='root',
       passwd='pass123', db='workbench', charset='utf8')


    cursor = conn.cursor()


    cursor.execute("""
         SELECT * FROM gene where gene_id AND `descrip` regexp "^@.*@.*$"
    LIMIT 1""")
    rows = cursor.fetchall()
    for row in rows:
       rows_new = pattern.match(row[1])
    if rows_new.group(1) is not None:
       cursor.execute("""
           update gene set descrip = %s where descrip = %s""",
    (rows_new.group(1), row[0]))
           #cursor.execute("""
           #update gene set descrip = %s where descrip = %s""",
    (rows_new.group(1), row[0]))


       conn.close()




    --
    Regards
    Boffin










    On Tue, Sep 17, 2013 at 1:05 PM, Ben Finney wrote:

    Venkat Addala <venkat.boffin@gmail.com> writes:
    I'm new to python
    Welcome! Congratulations on choosing Python for programming.
    i am connecting mysql database with python. now i want to do
    sanitation on the database, like to remove "\n", extra spaces blah
    blah.. and update it back to mysql database.
    i was trying somthing, here is my code, can you please provide me
    solution for this..
    Thank you for providing a small, complete example.

    You should also describe what behaviour you expect, and what behaviour
    you're getting instead. What happens, and how are you expecting it to be
    different?
    #!/usr/bin/python
    import MySQLdb as mdb

    con = mdb.connect('localhost', 'root', 'pass at 123', 'workbench');
    There's no need to end Python statements with a semicolon; it's only
    confusing to do it.
    rows = cur.fetchall()
    for row in rows:
    You never use ?rows? for anything else, so you may as well forget it and
    just iterate directly over the return value::

    for row in cur.fetchall():
    row_new = row[0].split("\n", " ")
    Read the documentation for ?str.split? to see what is wrong with the
    above call.


    You might also be interested in the ?python-tutor? forum, specially
    designed for beginners with basic questions about Python
    <URL:http://mail.python.org/mailman/listinfo/tutor>
    <URL:http://dir.gmane.org/gmane.comp.python.tutor>.

    Good hunting to you!

    --
    \ ?Sunday: A day given over by Americans to wishing that they |
    `\ themselves were dead and in Heaven, and that their neighbors |
    _o__) were dead and in Hell.? ?Henry L. Mencken |
    Ben Finney

    --
    https://mail.python.org/mailman/listinfo/python-list
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: <http://mail.python.org/pipermail/python-list/attachments/20130917/23692f3a/attachment.html>
  • Ervin Hegedüs at Sep 17, 2013 at 8:46 am
    Hello,

    On Tue, Sep 17, 2013 at 05:35:30PM +1000, Ben Finney wrote:
    Venkat Addala <venkat.boffin@gmail.com> writes:
    rows = cur.fetchall()
    for row in rows:
    You never use ?rows? for anything else, so you may as well forget it and
    just iterate directly over the return value::

    for row in cur.fetchall():

    and what if in body of iteration there is another fetchall()? :)


    I mean:
           for row in cur.fetchall():
             do_domething()
             cur.execute("SELECT * FROM another_table")




    Cheers:




    Ervin
  • Ben Finney at Sep 17, 2013 at 9:22 am

    Ervin Heged?s <airween@gmail.com> writes:


    Hello,
    On Tue, Sep 17, 2013 at 05:35:30PM +1000, Ben Finney wrote:
    for row in cur.fetchall():
    and what if in body of iteration there is another fetchall()? :)

    Yes, what if? Each call to ?fetchall? returns a sequence of rows. I
    don't see your point.


    --
      \ ?The most common way people give up their power is by thinking |
       `\ they don't have any.? ?Alice Walker |
    _o__) |
    Ben Finney
  • Ervin Hegedüs at Sep 17, 2013 at 10:05 am
    Hello,

    On Tue, Sep 17, 2013 at 07:22:50PM +1000, Ben Finney wrote:
    Ervin Heged?s <airween@gmail.com> writes:
    Hello,
    On Tue, Sep 17, 2013 at 05:35:30PM +1000, Ben Finney wrote:
    for row in cur.fetchall():
    and what if in body of iteration there is another fetchall()? :)
    Yes, what if? Each call to ?fetchall? returns a sequence of rows. I
    don't see your point.

    yep', sorry, I'm confused. I just remember - maybe badly - once I
    used this form, and the 'row' continues the new sequence... But
    now I tried, and I see this presumtion is wrong.


    Sorry for disturbing, and thanks :)




    a.
  • Ervin Hegedüs at Sep 17, 2013 at 8:41 am
    Hello,

    On Tue, Sep 17, 2013 at 12:43:20PM +0530, Venkat Addala wrote:
    Hi all,


    I'm new to python, i am connecting mysql database with python. now i want
    to do sanitation on the database, like to remove "\n", extra spaces blah
    blah.. and update it back to mysql database. i was trying somthing, here is
    my code, can you please provide me solution for this..

    #!/usr/bin/python
    import MySQLdb as mdb

    con = mdb.connect('localhost', 'root', 'pass at 123', 'workbench');

    a tip: you can pass the arguments to function as dictionary, so
    if you store the connection arguments anywhere, you use this
    form:


    conndsc = {'host': 'localhost', 'user': 'root', 'port': 3306, ....}
    con = mdb.connect(**conndsc)


    My personal opinion, but it's easyer to use the cursor if you
    have dictionary-like cursor:

    with con:
    cur = con.cursor()

    cur = con.cursor(mdb.cursors.DictCursor)


    In this case you can reference the name of the table column,
    instead of index of result, in your example:


    row['descrip']

    cur.execute("SELECT descrip FROM table LIMIT 1")
    rows = cur.fetchall()

    if you want to fetch only one row, you can use cur.fetchrow(),
    which gives only one row, and you don't need to iterate that

    for row in rows:
    row_new = row[0].split("\n", " ")
    row = "".join(row_new)

    as Benn Finney wrote, the use of split() above is wrong.
    I think you need some kinf of this:


    " ".join([f.strip() for f in row[0].replace("\n", " ").split()])


    but it depends what you want.




    Cheers:


    Ervin

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedSep 17, '13 at 7:13a
activeSep 17, '13 at 10:05a
posts7
users3
websitepython.org

People

Translate

site design / logo © 2022 Grokbase