Advertisement
Typhoon

PyMySQL Update E-Mail Records with 2 DB

May 26th, 2015
439
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 2.19 KB | None | 0 0
  1. #!/usr/bin/env python
  2. # -*- coding: utf-8 -*-
  3. import pymysql
  4.  
  5. # Connect to the source database
  6. connection = pymysql.connect(host='mariadb55.websupport.sk',
  7.                              port=3310,
  8.                              user='my_db_user',
  9.                              passwd='my_db_pass',
  10.                              db='my_db_name',
  11.                              charset='utf8mb4',
  12.                              cursorclass=pymysql.cursors.DictCursor)
  13.  
  14. # Select All E-Mails from Unregistered Database
  15. with connection.cursor() as cursor:
  16.         sql = "SELECT `email` FROM `mail_unregistered`"
  17.         cursor.execute(sql)
  18.         result = cursor.fetchall()
  19.        
  20. # Add it to list
  21. bad_emails = []
  22.  
  23. for x in result:
  24.     si_mail = str(x['email']).replace('\n','').replace(' ','')
  25.     bad_emails.append(si_mail)
  26.    
  27. # Close connection to Database
  28. cursor.close()
  29. connection.close()
  30.  
  31. print("\n### Prosessing... ###\n")
  32.  
  33. # Connect to second database
  34. connection = pymysql.connect(host='mariadb55.websupport.sk',
  35.                              port=3310,
  36.                              user='my_dbuser',
  37.                              passwd='my_db_pass',
  38.                              db='my_db_name',
  39.                              charset='utf8mb4',
  40.                              cursorclass=pymysql.cursors.DictCursor)
  41.  
  42. # Select All E-Mails from DB where Group ID is not equal 1 (In this case already Unsubscribered)
  43. with connection.cursor() as cursor:
  44.         sql = "SELECT `sub_mail` FROM `subscribers` WHERE `GID` != 1"
  45.         cursor.execute(sql)
  46.         result = cursor.fetchall()
  47.        
  48. # Add it to list
  49. good_emails = []
  50. for x in result:
  51.     si_mail = str(x['sub_mail']).replace('\n','').replace(' ','')
  52.     good_emails.append(si_mail)
  53.  
  54. # If E-Mail from first list (Unsubscribers) is in second list Update its record in Database with Group ID 1
  55. for mail in bad_emails:
  56.     if mail in good_emails :
  57.         print ("Duplicate : " + mail)
  58.         with connection.cursor() as cursor:
  59.             sql = "UPDATE `subscribers` SET `GID`=1 WHERE `sub_mail` LIKE %s"
  60.             cursor.execute(sql, (mail))
  61.             print(cursor.description)
  62.             print ("Group changed to Unsubscribers")
  63.     # else :
  64.     #     print ("\nUnique : " + mail)
  65.  
  66. # Close Connection
  67. cursor.close()
  68. connection.close()
  69. print("\n### Finished ###\n")
  70. print "DB Unsubscribers : " + str(len(bad_emails))
  71. print "DB Lethe : " + str(len(good_emails))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement