使用python在sqlite中同时向多个表插入数据

Question

我试图读入多个文件，用python填充sqlite中的多个表。我正试着做这样的事。在

def connect_db():
    return sqlite3.connect(app.config['DATABASE']) # Flask stuff 

# List of tables and corresponding filenames
file_list = ['A','B','C','D']

for file in file_list:
    with closing(connect_db()) as db:
        cursor = db.execute('select * from ' + file)
        # Get column names for the current table
        names = tuple(map(lambda x: x[0], cursor.description))
        filename = file + '.txt'
        with open(filename, 'r') as f:
            data = [row.split('\t') for row in f.readlines()]
            cur.executemany('INSERT INTO' + file + ' ' + str(names) + ' VALUES ' + ???)

现在我意识到在上面的executemany（）语句中，我需要将语法（？,?,?) 哪里？是列数。我可以生成一个元组？但是他们周围会有引号。('?', '?', '?'). 有办法吗？谢谢。在

Answer 1

您可以实现一个查询“maker”，该查询在dict或对象属性上循环，并用它构建值字符串。 我在某个项目上做过类似的事情：

    d = {
        "id":0,
        "name":"Foo",
        "bar":"foobar"
    }

    sql = ""
    for key, foo in d.iteritems():
        # First, copy the current value
        bar = copy.copy( foo )

        # Test if number
        try:
            # try to increase the value of 1
            bar += 1
        except TypeError:
            # if we can't, add double quote 
            foo = '"%s"' % foo

        # concat with the previous sequences
        sql = "%s%s=%s," % ( sql, key, foo )

    # remove the latest comma ...
    sql = sql[:-1]

所以循环末尾的“sql”值应该是这样的

bar="foobar",id=0,name="Foo"