python读取csv文件并导入到mysql数据库里

断断续续在学一点python的东西，怕学了之后就忘了，我写到博客园里存下来。

下面是我从网络上找的并自己修改后可以运行通过的。

import sys
import pymysql
import csv

def main(user, pwd, db, table, csvfile):

    try:
        conn = getconn(user, pwd, db)
    except Exception as e:
        print(e)
        sys.exit (1)

    cursor = conn.cursor()

    loadcsv(cursor, table, csvfile)

    cursor.close()
    conn.close()

def getconn(user, pwd, db):
    conn = pymysql.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__()
    #这里next函数可以读取csv文件的第一行数据并将光标移到下一列
    numfields = len(header)
    print(numfields)

    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)

1.一开始直接从网上找到这段代码的时候，直接运行，报错，一直找不到原因。

2.sys.argv[]这个函数一开始不懂http://blog.csdn.net/vivilorne/article/details/3863545 这里对这个函数进行了介绍。

转载于:https://www.cnblogs.com/foruby/p/4410100.html