Load CSV values then search for matches in mysql database and then apply insertion?

Question

I am using python Django and I have a CSV file which contains 4 fields name, abbreviation, address and type of school. I have 3 database table named university, type of university and university-type mapping.I am having a csv list which has name of university I want to compare it with university table present in database and want to find out id corresponding to that name and same process with type of university and find out its id.And then i want to insert those values into university-type mapping. I am able to read data from CVS using this method but stuck on database search for match: here is what i am using to read the search:

import csv

path = '/home/abi/Downloads'

file=open( path +"university.CSV", "r")
reader = csv.reader(file)
for line in reader:
    t=line[1],line[2]
    print(t)

NAME

NIT
Pune University
New University
Gujarat Technical  University
Kuwait University - College of Business Administration جامعة الكويت - كلية العلوم الادارية
testuniversity
testuniversity
testuniversity23
testuniversity
Mumbai university
Mumbai university
California University
California University
California University
California University
California University

this is the one field in csv

and i have university sql table with field 'name' and i want to compare it CSV name and get the corresponding id from it

>>> import csv
>>> import pymysql
>>> import pymysql.cursors 
>>> connection = pymysql.connect(user='root',password='root')

>>> path = '/home/abi/Downloads/'
>>> t=[]
>>> p=[]
>>> file  = open(path +"university_list.csv - university_list.csv.csv","r" )
>>> reader = csv.reader(file)
>>> for line in reader:
...     t=line[0]
...     p=line[3]
...     print t

@Frank i use pymysql and it worked but how can i compare my values of csv with database table — Abi Waqas
– Abi Waqas, Commented May 17, 2017 at 8:49

Frank · Accepted Answer · 2017-05-17 10:10:25Z

Maybe you can do like this:

import csv
import pymysql

path = '/home/abi/Downloads'

conn = pymysql.connect(host='127.0.0.1', port=3306, user='user',                 
passwd='pass', db='db_name', charset='utf8')
cursor1 = conn.cursor()
cursor2 = conn.cursor()
cursor3 = conn.cursor()

file=open( path +"university.CSV", "r")
reader = csv.reader(file)
for line in reader:
   csv_school_name = line[0]
   csv_school_type = line[3]
   cursor1.execute("select id from schools_names where school_name = s%", csv_school_name)
   corresponding_school_name_id = cursor1.fetchone()
   cursor2.execute("select id from schools_types where school_type = s%", csv_school_type)
   corresponding_school_type_id = cursor2.fetchone()

After this you can get school_name_id and school_type_id, then you can insert this two ids into table if you want to do. I hope this can help you.

you must execute

cursor1.execute("select id from schools_names where school_name = s%", csv_school_name)

ont time, then use fetchone(), if you use

school_id = cursor1.execute("select id from schools_names where school_name = s%", csv_school_name)

you will get count of table.

it worked frank but it is showing total count of school table instead of id that is what I am checking at the moment...thanks a lot solved some of my purpose
sorry to disturb u again I m using your query but it is outputting some garbage value in output...0 0 0 0 0 0 0 0 0 0 0 1 so, is it happening because some of the school names are in Arabic?

Collectives™ on Stack Overflow

Load CSV values then search for matches in mysql database and then apply insertion?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related