在mysq中导入csv文件

Question

我有一个数据库，里面有表：个人、球员、教练和球队。所有表都有一个自动递增的id字段作为主键。此人有id，firstname，lastname。球员和教练都有id字段，以及person_id和team_id作为外键将他们绑定到团队.id或者人.id其他表中的字段。 我有一个主csv文件，我想从中导入MySql中的所有值，不同的表有id。 我还要检查数据库中的值。如果值在数据库中，则不要导入该值。 我使用了CSV解析和索引功能。但我做不到。有人能帮我吗 下面是我的sql表

mysql> describe person;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(30) | NO   |     | NULL    |                |
| lastname  | varchar(30) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
mysql> describe player;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| person_id | int(11) | NO   | MUL | NULL    |                |
| team_id   | int(11) | NO   | MUL | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
mysql> describe team;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| teamname  | varchar(25) | NO   |     | NULL    |                |
| location  | varchar(40) | NO   |     | NULL    |                |
| city      | varchar(25) | NO   |     | NULL    |                |
| state     | varchar(2)  | NO   |     | NULL    |                |
| venue     | varchar(35) | NO   |     | NULL    |                |
| league_id | int(11)     | NO   | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

我的Csv文件是 名字姓氏teamname位置城市州|场地
abc cdf印度csv bng kar abc

导入后

我有一个数据库，里面有表：个人、球员、教练和球队。所有表都有一个自动递增的id字段作为主键。此人有id，firstname，lastname。球员和教练都有id字段，以及person_id和team_id作为外键将他们绑定到团队.id或者人.id其他表中的字段。 我有一个主csv文件，我想从中导入MySql中的所有值，不同的表有id。 我还要检查数据库中的值。如果值在数据库中，则不要导入该值。 我使用了CSV解析和索引功能。但我做不到。有人能帮我吗 下面是我的sql表

^{pr2}$

我的Csv文件是

First Name  Last Name   teamname    Location    city    state   |venue  
abc cdf india   csv bng kar abc

导入后

id First Name   Last Name   teamname    Location    city    state   |venue  coment
1   1   1   1   1   1   1 abc abc

我试着用一些小代码

 # initialize with empty ints and dicts
        name,cities,countries,states=[],[],[],[]

        with open('ind.csv','rb') as csvfile:
            reader = csv.reader(csvfile, delimiter=',')
            reader.next() #skip header
            for row in reader:
                name.append(row[0])
                cities.append(row[2])
                states.append(row[3])
                countries.append(row[4])
        cl = list(set(countries))
        sl = list(set(states))
        citl = list(set(cities))
        inf1 = list(set(name)) 



        with open('countries.csv','w') as cfile:
            writer = csv.writer(cfile, delimiter=',')
            writer.writerow(['country_id','name'])
            for i,x in enumerate(cl):
                writer.writerow([i,x])

        with open('state.csv','w') as cfile:
            writer = csv.writer(cfile, delimiter=',')
            writer.writerow(['state_id','country_id','state'])
            for i,x in enumerate(sl):
                writer.writerow([i,x,cl.index(countries[states.index(x)])])

        with open('cities.csv','w') as cfile:
            writer = csv.writer(cfile,delimiter=',')
            writer.writerow(['city_id','city','st_id','country_id'])
            for i,x in enumerate(citl):
                writer.writerow([i,x,sl.index(states[cities.index(x)]),
                                 cl.index(countries[cities.index(x)])
                                 ])


        with open('inf123.csv','w') as cfile:
            writer = csv.writer(cfile,delimiter=',')
            writer.writerow(['Name_id', 'Name','city_id','st_id','country_id'])
            for i,x in enumerate(inf1):
                writer.writerow([i,x,
                                citl.index(cities[name.index(x)]),
                                sl.index(states[name.index(x)]),
                                cl.index(countries[name.index(x)])

                                 ])

        import MySQLdb 
        import csv
        mydb = MySQLdb.connect(host="localhost", # The Host 
        user="root", # username 
        passwd="root", # password 
        db="abcm") # name of the data base


        cursor = mydb.cursor()

        csv_data = csv.reader(file('countries.csv'))
        for row in csv_data:

            cursor.execute('INSERT INTO country(id, \
                  name )' \
                  'VALUES("%s", "%s")', 
                  row)
        #close the connection to the database.
        mydb.commit()
        cursor.close()
        print "Done"


        cursor = mydb.cursor()

        csv_data = csv.reader(file('state.csv'))
        for row in csv_data:

            cursor.execute('INSERT INTO state(id, \
                  country, name )' \
                  'VALUES("%s", "%s", "%s")', 
                  row)
        #close the connection to the database.
        mydb.commit()
        cursor.close()
        print "Done"    

Answer 1

I have one master csv file, from that I want import all the values in MySql different tables with ids.

这是不可能的，因为导入例程不知道要将数据放在哪里。在

如果您的csv主文件包含一个包含表名的列，则可以

• 将csv文件导入临时
• 使用不同的sql语句将数据移动到适当的表中