Newbie needing some help

[Chris Kaynor]

On Fri, Aug 8, 2014 at 12:07 PM, Matt Smith <smithmm at tblc.org> wrote:

> 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')
>

You probably should use the with statement to make sure the file closes,
like follows:

with open('/home/smithm/email-list.txt', 'r') as f:
    # Code needing the file goes here.


>  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()
>

You probably want you connect to the database outside the line for
performance reasons.



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

This line also seems suspect - you are not merging in the line to the SQL
statement. If you meant:

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

you would have a SQL injection attack possible. The sql library should have
support for this type of loading the code. I do not know the exact syntax
for how to do this within the mysql connector library, but typically you
pass the arguments to the execute command (in this case, line) with some
formatting in the sql command line..


>         try:
>           # Execute the SQL command
>           cursor.execute(sql)
>           # Commit your changes in the database
>           db.commit()
>         except:
>

As a heads up, bare exceptions are generally a bad idea, however in this
case they are acceptable.

However, I'd recommend re-raising the exception after rolling back the
transaction with a bare "raise" statement right after the db.rollback() -
at the absolute minimum, you should log the error. This will likely let you
see what your problem is.


>           # Rollback in case there is any error
>           db.rollback()
>
>
I'd probably put the close inside of a finally block (especially if you
re-raise the exception as suggested above).


>         # disconnect from server
>         db.close()
>


Overall, I'd suggest restructing your code to look like (untested):

import mysql.connector
with open('/home/smithm/email-list.txt', 'r') as f:
    # Open database connection
    db = mysql.connector.connect(user="xx", password="xx",
host="localhost", database="xx")
    try:
        # prepare a cursor object using cursor() method
        cursor = db.cursor()
        for line in f:
            #<do something with line>
            # NOTE: the mysql library might support the with statement like
above, removing the need for the try...finally.
            try:
                # Prepare SQL query to DELETE required records
                sql = "DELETE FROM tblc_users WHERE user_email=%s" ## --I
do not know if "%s" is the correct syntax for this library.--
                # Execute the SQL command
                cursor.execute(sql, line) ## --I do not know if this is the
correct syntax for this library.--
                # Commit your changes in the database
                db.commit()
            except:
                # Rollback in case there is any error
                db.rollback()
                raise
    finally:
        # disconnect from server
        db.close()


Chris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20140808/b6197832/attachment.html>