如何通过executemany（）语句转换要插入的pandas数据帧？

Question

我有一个相当大的熊猫dataframe - 50头和几十万行数据-我希望使用ceODBC模块将这些数据传输到数据库。以前我使用pyodbc并在for循环中使用一个简单的execute语句，但这需要非常长的时间（每10分钟1000条记录）。。。

我现在正在尝试一个新的模块，并尝试引入executemany()，尽管我不太确定以下参数序列的含义：

    cursor.executemany("""insert into table.name(a, b, c, d, e, f) 
values(?, ?, ?, ?, ?), sequence_of_parameters)

它是否像一个常量列表，在每个头中工作

    ['asdas', '1', '2014-12-01', 'true', 'asdasd', 'asdas', '2', 
'2014-12-02', 'true', 'asfasd', 'asdfs', '3', '2014-12-03', 'false', 'asdasd']

• 这里是三行的例子

或者需要什么格式？

另一个相关的问题是，如何将常规pandas数据帧转换为这种格式？

谢谢！

Answer 1

我终于弄明白了。 因此，如果您有一个Pandas数据帧，希望使用ceODBC（我使用的模块）将其写入数据库，那么代码是：

（使用all_data作为数据帧）将数据帧值映射到字符串，并将每一行作为元组存储在元组列表中

for r in all_data.columns.values:
    all_data[r] = all_data[r].map(str)
    all_data[r] = all_data[r].map(str.strip)   
tuples = [tuple(x) for x in all_data.values]

对于元组列表，将所有空值指示符（在上面的转换中被捕获为字符串）更改为可以传递到最终数据库的空类型。这是我的问题，可能不是你的问题。

string_list = ['NaT', 'nan', 'NaN', 'None']

def remove_wrong_nulls(x):
    for r in range(len(x)):
        for i,e in enumerate(tuples):
            for j,k in enumerate(e):
                if k == x[r]:
                    temp=list(tuples[i])
                    temp[j]=None
                    tuples[i]=tuple(temp)

remove_wrong_nulls(string_list)

创建到数据库的连接

cnxn=ceODBC.connect('DRIVER={SOMEODBCDRIVER};DBCName=XXXXXXXXXXX;UID=XXXXXXX;PWD=XXXXXXX;QUIETMODE=YES;', autocommit=False)
cursor = cnxn.cursor()

定义一个函数，将元组列表转换为new_list，这是对元组列表的进一步索引，将其转换为1000个块。这对于我将数据传递到SQL查询不能超过1MB的数据库是必要的。

def chunks(l, n):
    n = max(1, n)
    return [l[i:i + n] for i in range(0, len(l), n)]

new_list = chunks(tuples, 1000)

定义查询。

query = """insert into XXXXXXXXXXXX("XXXXXXXXXX", "XXXXXXXXX", "XXXXXXXXXXX") values(?,?,?)"""

运行包含1000组元组列表的new_list，并执行executemany。接下来提交并关闭连接，就这样：）

for i in range(len(new_list)):
    cursor.executemany(query, new_list[i])
cnxn.commit()
cnxn.close()

Answer 2

你可以试试这个：

cursor.executemany(sql_str, your_dataframe.values.tolist())

希望有帮助。

Answer 3

回答这个问题可能有点晚，但也许它仍然可以帮助别人。executemany()不是由许多ODBC实现的。其中一个是MySQL。当它们指的是参数序列时，它们的意思是：

parameters=[{'name':'Jorge', 'age':22, 'sex':'M'}, 
            {'name':'Karen', 'age':25, 'sex':'F'}, 
            {'name':'James', 'age':29, 'sex':'M'}]

对于查询语句，它看起来像：

SQL = INSERT IGNORE INTO WORKERS (NAME, AGE, SEX) VALUES (%(name)s, %(age)s, %(sex)s)

看起来你到了。不过，我想指出几点，以防万一： pandas有一个to_sql函数，如果您向它提供connector对象，它会插入到db中，并将数据分块。

要从pandas数据帧快速创建参数序列，我发现以下两种方法很有用：

# creates list of dict, list of parameters
# REF: https://groups.google.com/forum/#!topic/pydata/qna3Z3WmVpM
parameters = [df.iloc[line, :].to_dict() for line in range(len(df))]

# Cleaner Way
parameters = df.to_dict(orient='records')