On Fri, Aug 8, 2014 at 12:07 PM, Matt Smith <smit...@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
-- https://mail.python.org/mailman/listinfo/python-list