Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #75906 > unrolled thread

Newbie needing some help

Back to article view | Back to comp.lang.python

Contents

  Newbie needing some help Matt Smith <smithmm@tblc.org> - 2014-08-08 15:07 -0400
    Re: Newbie needing some help John Gordon <gordon@panix.com> - 2014-08-09 02:51 +0000
      Re: Newbie needing some help Chris Angelico <rosuav@gmail.com> - 2014-08-09 13:03 +1000

#75906 — Newbie needing some help

[Multipart message — attachments visible in raw view] — view raw

I am trying to write a program that will loop through a text file and
delete rows in a mysql database.

It seemingly runs but I don't see anything getting deleted in the db.
Is there anything apparent that I am missing?

This is the code:
#!/usr/bin/python
import mysql.connector
#
f=open('/home/smithm/email-list.txt', 'r')
for line in f:
        #<do something with line>
        # Open database connection
        db = mysql.connector.connect(user="xx", password="xx",
host="localhost", database="xx")

        # prepare a cursor object using cursor() method
        cursor = db.cursor()

        # Prepare SQL query to DELETE required records
        sql = "DELETE FROM tblc_users WHERE user_email=%s, % (line)"
        try:
          # Execute the SQL command
          cursor.execute(sql)
          # Commit your changes in the database
          db.commit()
        except:
          # Rollback in case there is any error
          db.rollback()

        # disconnect from server
        db.close()

-- 
Matthew Smith

[toc] | [next] | [standalone]

#75927

In <mailman.12762.1407533916.18130.python-list@python.org> Matt Smith <smithmm@tblc.org> writes:

> I am trying to write a program that will loop through a text file and
> delete rows in a mysql database.

> It seemingly runs but I don't see anything getting deleted in the db.
> Is there anything apparent that I am missing?

> This is the code:
> #!/usr/bin/python
> import mysql.connector
> #
> f=open('/home/smithm/email-list.txt', 'r')
> for line in f:
>         #<do something with line>
>         # Open database connection
>         db = mysql.connector.connect(user="xx", password="xx",
> host="localhost", database="xx")
>         # prepare a cursor object using cursor() method
>         cursor = db.cursor()

As others have said, this code connects to the database and prepares
a cursor *every time* through the loop.  You probably only want to
do those things once.

>         # Prepare SQL query to DELETE required records
>         sql = "DELETE FROM tblc_users WHERE user_email=%s, % (line)"

You don't have the sql command constructed quite right.  As written, the
sql command will be, literally:

    DELETE FROM tblc_users WHERE user_email=%s, % (line)

And of course that likely won't match anything.  (As a very simple debugging
step, you could have printed the sql statement each time through the loop.
That would have alerted you immediately as to what was going on.)

You probably meant something like this instead:

    sql = "DELETE FROM tblc_users WHERE user_email=%s" % line

This will substitute the value of line for the %s.

However, most (all?) SQL databases require string values to be enclosed
in single quotes, and your databse likely defines user_email as a string
value.  So you probably actually want something like this:

    sql = "DELETE FROM tblc_users WHERE user_email='%s'" % line

And even this solution isn't very good, because it allows SQL injection
attacks if your text file contains something nasty.  If this is anything
other than a toy program, please take the time to look up prepared
statements.

>         try:
>           # Execute the SQL command
>           cursor.execute(sql)
>           # Commit your changes in the database
>           db.commit()
>         except:
>           # Rollback in case there is any error
>           db.rollback()

Again, as others have said, using a bare 'except:' statement will catch
and hide any possible error, leaving you mystified as to why nothing
happened.

>         # disconnect from server
>         db.close()

-- 
John Gordon         Imagine what it must be like for a real medical doctor to
gordon@panix.com    watch 'House', or a real serial killer to watch 'Dexter'.

[toc] | [prev] | [next] | [standalone]

#75930

On Sat, Aug 9, 2014 at 12:51 PM, John Gordon <gordon@panix.com> wrote:
> You probably meant something like this instead:
>
>     sql = "DELETE FROM tblc_users WHERE user_email=%s" % line
>
> This will substitute the value of line for the %s.
>
> However, most (all?) SQL databases require string values to be enclosed
> in single quotes, and your databse likely defines user_email as a string
> value.  So you probably actually want something like this:
>
>     sql = "DELETE FROM tblc_users WHERE user_email='%s'" % line
>
> And even this solution isn't very good, because it allows SQL injection
> attacks if your text file contains something nasty.  If this is anything
> other than a toy program, please take the time to look up prepared
> statements.

All SQL databases require strings to be quoted (it's part of the SQL
spec), although some broken database engines (which I shall not name)
do allow other forms of quote than the apostrophe. But I would advise
against even suggesting the interpolation method; there's absolutely
no reason ever to do this sort of thing - it's just way too fragile.
(Even if you think you can get it perfectly right now, do you really
want to inflict the headache on the code's next maintainer?)
Parameterized queries are a part of the Python database API, so go
ahead and use them.

ChrisA

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web