Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #modules we use throughout this script
- import csv
- import mysql.connector
- import fileinput
- import urllib
- import sys
- #connection info for the database
- cnx = mysql.connector.connect(host="localhost", user="root", password="pass", database="test")
- query = cnx.cursor(prepared=True)
- #Here we download the Robocallers .csv file, the string at the end of the URL are filters passed to the API to only pull down the data we need
- download = urllib.URLopener()
- download.retrieve("urltofccAPI", "RoboCaller.csv")
- #add test to see if file downloaded or not, if file did not then sys.exit()
- #Here build a filterd .csv to prevent any duplicate records as well as skip the first line of the .csv file as it contains field names
- map = {}
- row_count = 0
- with open(r'RoboCaller.csv', 'r') as infile:
- reader = csv.reader(infile)
- for row in reader:
- (caller_id_number, issue, issue_date) = row
- row_count = row_count + 1
- if row_count == 1:
- continue
- if map.has_key(caller_id_number):
- continue
- else:
- map[caller_id_number] = (issue, issue_date)
- #at this point we count our Filtered CSV file to see if its massivly truncated.
- #we check against a threshhold to see if the amount of records are less than it.
- #if they are we close the program and throw an error if not we continue.
- if row_count < 80000:
- print('this list is far to truncated, terminating program')
- sys.exit()
- #Here we check the count of the table and store it
- query.execute("Select count(*) from Numbers")
- oldcount = query.fetchall()
- print ("The old count is:" + str(oldcount))
- #Here we query the db to build the whitelist as well as exclude numbers we own
- query.execute("Select whitelist FROM whitelist")
- whitelist = [entry for (entry,) in query.fetchall()]
- #print("the numbers" + str(whitelist) + "are on the white list and will be skipped")
- query.execute("Select TN from Numbers")
- didnumbers = [entry for (entry,) in query.fetchall()]
- #print("the numbers" + str(didnumbers) + "are apart of the numbers we own and will be omitted from the black list")
- #here were Purge the current value in the mysql DB table, so we dont build duplicates
- query.execute("TRUNCATE TABLE Numbers")
- #here we check every Key in the Dict(Caller_Id_Number is our Key) as well as the tuples (v) accociated each key (the tuples are issue & issue_date)
- for caller_id_number, v in map.iteritems():
- caller_id_number = caller_id_number[:3] + caller_id_number[4:7] + caller_id_number[8:12]
- if caller_id_number in whitelist:
- print("Phone Number " + str(caller_id_number) + " is in the whitelist and will be ignored")
- continue
- elif caller_id_number in didnumbers:
- print("Phone Number " + str(caller_id_number) + " is a number you Own and will be ignored")
- continue
- else:
- query.execute("INSERT INTO Numbers ( Numbers, issue, date ) VALUES(%s,%s,%s)", (caller_id_number, v[0], v[1][:10])) #sql query to insert numbers into black list
- #Here we check the new count
- query.execute("Select count(*) from Numbers")
- newcount = query.fetchall()
- print ("The new Count is:" + str(newcount))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement