CODEWITHSUNDEEP
python-3.xmariadb
will.ditch
will.ditch

Reputation: 49

Querying MariaDB via file in python

I have a project I'm working on where I would like to query MariaDB via a file line by line. It is all written in python. The problem I'm having is it will only print the last line if found, not all matches. My expectation is that it prints to the terminal every time it finds a match. Any help is greatly appreciated.

import mysql.connector
import time
file1 = open("sample.unique.big", "r") 
count = 0

mydb = mysql.connector.connect(
host="10.0.0.72",
user="admin",
password="#########",
database="#########"
)
start = time.time()
while True: 
count += 1

# Get next line from file 
line = file1.readline() 

# if line is empty 
# end of file is reached 
if not line: 
    break
#print("{}".format(line.strip()))
#print("Line{}: {}".format(count, line.strip())) 
mycursor = mydb.cursor()
sql = "SELECT * FROM ether2 use index (idx_privadd) WHERE privadd = %s"
adr = (line, )
mycursor.execute(sql, adr)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)
end = time.time()
print(end-start)

Upvotes: 0

Views: 85

Answers (1)

will.ditch
will.ditch

Reputation: 49

i achieved my desired results by first formating the readline. Secondly, an if statement for myresults. Completed code looks like

import mysql.connector
import time
file1 = open("sample.unique.big", "r") 
count = 0

mydb = mysql.connector.connect(
host="10.0.0.72",
user="*****",
password="*****",
database="etherpro"
)
start = time.time()
while True: 
    count += 1
    # Get next line from file 
    line = file1.readline() 
    #Format the line
    search=("{}".format(line.strip()))
# if line is empty 
# end of file is reached 
    if not line: 
        break
    mycursor = mydb.cursor()
    sql = "SELECT id FROM ether2 WHERE privadd = %s"
    adr = (search, )
    mycursor.execute(sql, adr)
    myresults = mycursor.fetchone()
    if myresults:
        print("oh snap, the id is:",myresults)
end = time.time()
print(end-start)

Upvotes: 1

Related Questions