python - python sqlalchemy可以使用变量构造插入语句(针对mysql数据库)

Question

预先感谢您对此问题的建议...

我正在尝试创建一个 python 脚本来将一组 CSV 导入到 mysql 数据库中。

每个 CSV 文件名都与目标表匹配。每个 CSV 的第一行与表的字段匹配。每个CSV/表都有不同的字段数量、字段名称等。

我遇到的问题是这一行(完整代码如下)

ins = table_name.insert().values(temp_variable_name)

我想动态更新目标表(table_name)和插入命令(temp_variable_name)。

因此，当读取 labels.csv 文件时，这应该会产生

ins = labels.insert().values(id_label=d[0], label_name=d[1])

当读取company.csv文件时，这应该会产生

ins = company.insert().values(id_company=d[0], company_name=d[1], ticker=d[2])

问题是如果我生成一个字符串，

temp_variable_name = 'id_company=d[0], company_name=d[1], ticker=d[2]'

我最终收到“str”对象没有属性“items”错误。

有没有办法动态生成SQL语句的插入命令？

以下脚本的一部分:

# files list contains a list of all of the files in the directory
# we read in CSVs, isolate the first row to determine table field names
# the rest of the data should then be imported into the table with the corresponding name as the CSV

for f in files:
    if '.csv' in f :

        # read in each CSV file

        # these are a Class / Function I've set up to read files
        x = Read_Files()  
        data = x.read_file_lines_strip(path, f)

        temp = data[0].replace('"','') # get rid of quotation marks from the data

        table_header_list = temp.split('|') # get the first row, which is the table field names

        variable_name ='' # this is used to construct the insert into table string

        for x in xrange (0, len(table_header_list)) :
            if x == 0 :
                variable_name = variable_name + table_header_list[0] + '=d[0]'
            elif x == len(table_header_list) :
                variable_name = variable_name + table_header_list[x] + '=d[' + str(x) + ']'
            else :
                variable_name = variable_name + ', ' + table_header_list[x] + '=d[' + str(x) + ']'

        table_name = f.replace('.csv','') # remove the .csv from filename to isolate the file name, which is the same as table name

        # data from file

        for data_line in data[1:] :
            data_line = data_line.replace('"', '') # remove quotation marks
            d = data_line.split('|') # split the line which is delimited by a |

            # used to construct the final insert string
            for x in xrange(0, len(table_header_list)) :
                if x == 0 :
                    temp_variable_name = variable_name.replace('d[0]', d[0])
                else :
                    temp_variable_name = temp_variable_name.replace('d[' + str(x) + ']', d[x])


            try:
                # table name is the table to insert into, via the CSV filename
                # temp_variable_name is the insert string, such as 'id_company=d[0], company_name=d[1], ticker=d[2]'
                ins = table_name.insert().values(temp_variable_name)
                result = conn.execute(ins)
            except Exception, e :
                print 'error : ' + str(e)

Answer 1

您可以使用Insert来做到这一点对象和 csv模块，可以轻松使用 DictReader类(class)。以下是公司表的示例:

import csv
from sqlalchemy import create_engine
from sqlalchemy.sql import table, column

NULL_FIELD_VALUE = r'\N'
DB_CONNECT = 'sqlite:///company.db'
engine = create_engine(DB_CONNECT, echo=True)
conn = engine.connect()

with open('company.csv') as csvfile:
    reader = csv.DictReader(csvfile, delimiter='|')
    insert_table = table('company',
                         *[column(field) for field in reader.fieldnames])
    insert_dict = [{k: None if v == NULL_FIELD_VALUE else v
                        for k,v in row.items()}
                            for row in reader]
    conn.execute(insert_table.insert(), insert_dict)