Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.mixmin.net!feeds.phibee-telecom.net!newsfeed.xs4all.nl!newsfeed2a.news.xs4all.nl!xs4all!news.tele.dk!news.tele.dk!small.news.tele.dk!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail Return-Path: X-Original-To: python-list@python.org Delivered-To: python-list@mail.python.org X-Spam-Status: OK 0.003 X-Spam-Evidence: '*H*': 0.99; '*S*': 0.00; 'syntax': 0.04; '(especially': 0.07; 'finally:': 0.07; 'subject:help': 0.08; 'arguments': 0.09; 'cursor': 0.09; 'except:': 0.09; 'formatting': 0.09; 'merging': 0.09; 'rows': 0.09; 'try:': 0.09; 'runs': 0.10; 'cc:addr:python-list': 0.11; 'suggest': 0.14; 'changes': 0.15; '"%s"': 0.16; 'disconnect': 0.16; 'line)': 0.16; 'missing?': 0.16; 'statement.': 0.16; 'exception': 0.16; 'wrote:': 0.18; 'code.': 0.18; 'library': 0.18; 'trying': 0.19; 'commit': 0.19; 'skip:f 30': 0.19; 'seems': 0.21; 'command': 0.22; 'import': 0.22; '(in': 0.22; 'aug': 0.22; 'cc:addr:python.org': 0.22; 'error': 0.23; 'library,': 0.24; 'cc:2**0': 0.24; 'query': 0.26; 'suggested': 0.26; 'pass': 0.26; 'code:': 0.26; 'header:In-Reply-To:1': 0.27; 'correct': 0.29; 'chris': 0.29; 'generally': 0.29; 'raise': 0.29; 'absolute': 0.30; 'statement': 0.30; 'message-id:@mail.gmail.com': 0.30; 'code': 0.31; 'getting': 0.31; 'exceptions': 0.31; 'idea,': 0.31; 'loading': 0.31; 'seemingly': 0.31; 'subject:some': 0.31; 'file': 0.32; 'probably': 0.32; 'skip:m 30': 0.32; 'text': 0.33; 'open': 0.33; 'fri,': 0.33; 'skip:# 10': 0.33; 'skip:d 20': 0.34; "i'd": 0.34; 'received:74.125.82': 0.34; 'problem': 0.35; 'connection': 0.35; 'possible.': 0.35; 'transaction': 0.35; 'something': 0.35; 'case,': 0.35; 'prepare': 0.35; 'but': 0.35; 'received:google.com': 0.35; 'there': 0.35; 'method': 0.36; 'should': 0.36; 'error.': 0.37; 'performance': 0.37; 'server': 0.38; 'skip:& 10': 0.38; 'follows:': 0.38; 'skip:m 40': 0.38; 'pm,': 0.38; 'anything': 0.39; 'received:74.125': 0.39; 'bad': 0.39; 'delete': 0.39; 'sure': 0.39; 'skip:p 20': 0.39; 'how': 0.40; 'skip:u 10': 0.60; 'above,': 0.60; 'is.': 0.60; 'removing': 0.60; 'up,': 0.60; 'skip:o 30': 0.61; 'back': 0.62; '8bit%:10': 0.64; 'finally': 0.65; 'needing': 0.65; 'within': 0.65; 'note:': 0.66; 'close': 0.67; 'skip:m 50': 0.68; 'smith': 0.68; 'records': 0.73; '\xc2\xa0\xc2\xa0': 0.74; 'bare': 0.84; 'overall,': 0.84; 'apparent': 0.91 X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc:content-type; bh=/eNqlU8f8eIXaEVORWtiqjYndUK7uknRhFeg9lkNMY8=; b=W+YiWEQbW6zdvA4QlphaXj0I2AovORJ9xbwgab+w7Vkg5EWXAtVANs4w64KJvjdmDU IN1tQFkf/H1+m1VJjgi43t3A5TlnG6krNG+aNvzg8EgOXv1Cvu7S6YMX7gKyUq9F0zZU pDZghiitJ4W7jkiiEQhgB4iFThHY6LTlMVmn9ySh6xJ9FDr29aDh8YvjT3bvvq2PVAVJ v0rq+JOVZATP2jTjJVYGdGomxmBaBij0bNJaZAqctojBiDEmclbHXrVNm0OgTorbDc/j YdppM6B9YGFjP5f/czdTNUb2HutFsP+PAa1ePoQLZTP4K6MwwA/R5qqQ6XRrwWtXJLVN PFxw== X-Gm-Message-State: ALoCoQlrOXqBeO+28icjz3kllMMH2f4YARPZfnNIv95sLEVTL3F+zoY1jkYLKZJNyi5cUMTCeeUh X-Received: by 10.194.187.4 with SMTP id fo4mr34802119wjc.35.1407535292389; Fri, 08 Aug 2014 15:01:32 -0700 (PDT) MIME-Version: 1.0 In-Reply-To: References: From: Chris Kaynor Date: Fri, 8 Aug 2014 15:01:12 -0700 Subject: Re: Newbie needing some help To: Matt Smith Content-Type: multipart/alternative; boundary=047d7bb044e21cbd9905002559d2 Cc: "python-list@python.org" X-BeenThere: python-list@python.org X-Mailman-Version: 2.1.15 Precedence: list List-Id: General discussion list for the Python programming language List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Newsgroups: comp.lang.python Message-ID: Lines: 273 NNTP-Posting-Host: 2001:888:2000:d::a6 X-Trace: 1407538824 news.xs4all.nl 2835 [2001:888:2000:d::a6]:43909 X-Complaints-To: abuse@xs4all.nl Xref: csiph.com comp.lang.python:75914 --047d7bb044e21cbd9905002559d2 Content-Type: text/plain; charset=UTF-8 On Fri, Aug 8, 2014 at 12:07 PM, Matt Smith 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: > # > # 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: # # 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 --047d7bb044e21cbd9905002559d2 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On F= ri, Aug 8, 2014 at 12:07 PM, Matt Smith <smithmm@tblc.org> wr= ote:
I am trying to write a program that will loop through a te= xt file and delete rows in a mysql database.

It seemingly runs but I don= 9;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=3Dopen('/home/smithm/email-list.txt&#= 39;, '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:
=C2=A0 =C2=A0 # Code needing the file goes here.
=C2=A0
for line in f:
=C2=A0 =C2=A0 =C2=A0 =C2=A0 #<do something= with line>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 # Open database connec= tion
=C2=A0 =C2=A0 =C2=A0 =C2=A0 db =3D mysql.connector.connect(u= ser=3D"xx", password=3D"xx", host=3D"localhost&quo= t;, database=3D"xx")

=C2=A0 =C2=A0 =C2=A0 =C2=A0 # prepare a cursor object u= sing cursor() method
=C2=A0 =C2=A0 =C2=A0 =C2=A0 cursor =3D db.cu= rsor()
=C2=A0
You p= robably want you connect to the database outside the line for performance r= easons.
=C2=A0
=C2=A0
<= div>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 # Prepare SQL query to DELETE requir= ed records=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 sql =3D "DELETE= FROM tblc_users WHERE user_email=3D%s, % (line)"

This line also seems suspect - you are = not merging in the line to the SQL statement. If you meant:
sql =3D &= quot;DELETE FROM tblc_users WHERE user_email=3D%s" % (line)
you= would have a SQL injection attack possible. The sql library should have su= pport 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 formatt= ing in the sql command line..
=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 try:
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 # Execute the SQL command
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 cursor.execute(sql)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # Comm= it your changes in the database
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 db.commit()
=C2=A0 =C2=A0 =C2=A0 =C2=A0 except:

As a heads up, bare exce= ptions are generally a bad idea, however in this case they are acceptable.<= /div>

However, I'd recommend re-raising the exceptio= n after rolling back the transaction with a bare "raise" statemen= t right after the db.rollback() - at the absolute minimum, you should log t= he error. This will likely let you see what your problem is.
=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # Rollback in case there is any err= or
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 db.rollback()

I'd probably = put the close inside of a finally block (especially if you re-raise the exc= eption as suggested above).
=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 # disconnect from server
=C2=A0 =C2=A0 =C2=A0 =C2=A0 db.close()
=C2=A0

Overall, I'd suggest restruct= ing your code to look like (untested):

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


Chris=C2=A0
--047d7bb044e21cbd9905002559d2--