Newbie needing some help

[John Gordon]

In <mailman.12762.1407533916.18130.python-list at python.org> Matt Smith <smithmm at 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 at panix.com    watch 'House', or a real serial killer to watch 'Dexter'.