Mysql自带有local infile这个SQL语句可以导入，比如
load data local infile 'uniq.csv' into table tblUniq
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)
但是有时候mysql的server关闭了--local-infile，导致这个命令会遇到"the used command is not allowed with this mysql version" 这样的错误。
一个简单的方法是重启mysql-server并打开上述开关，或者可以自己写一个脚本导入数据，以免打扰工作众的MySQL
这里有个简单的脚本，http://128.174.125.122/wiki/index.php/Python_script_for_loading_CSV_to_mySQL
以下略作修改，以便输入密码。
（注意：密码最好加上双引号）
#!/usr/bin/env python
# Run with no args for usage instructions
#
# Notes:
# - will probably insert duplicate records if you load the same file twice
# - assumes that the number of fields in the header row is the same
# as the number of columns in the rest of the file and in the database
# - assumes the column order is the same in the file and in the database
#
# Speed: ~ 1s/MB
#
import sys
import MySQLdb
import csv
def main(user, pwd, db, table, csvfile):
try:
conn = getconn(user, pwd, db)
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)
cursor = conn.cursor()
loadcsv(cursor, table, csvfile)
cursor.close()
conn.close()
def getconn(user, pwd, db):
conn = MySQLdb.connect(host = "localhost",
user = user,
passwd = pwd,
db = db)
return conn
def nullify(L):
"""Convert empty strings in the given list to None."""
# helper function
def f(x):
if(x == ""):
return None
else:
return x
return [f(x) for x in L]
def loadcsv(cursor, table, filename):
"""
Open a csv file and load it into a sql table.
Assumptions:
- the first line in the file is a header
"""
f = csv.reader(open(filename))
header = f.next()
numfields = len(header)
query = buildInsertCmd(table, numfields)
for line in f:
＃避免多余空行影响
if len(line)<1: continue
vals = nullify(line)
cursor.execute(query, vals)
return
def buildInsertCmd(table, numfields):
"""
Create a query string with the given table name and the right
number of format placeholders.
example:
>>> buildInsertCmd("foo", 3)
'insert into foo values (%s, %s, %s)'
"""
assert(numfields > 0)
placeholders = (numfields-1) * "%s, " + "%s"
query = ("insert into %s" % table) + (" values (%s)" % placeholders)
return query
if __name__ == '__main__':
# commandline execution
args = sys.argv[1:]
if(len(args) < 5):
print "error: arguments: user \"password\" db table csvfile"
sys.exit(1)
main(*args)
Like this:
Like Loading...
Related