使用Python将数据帧插入MS-SQL时出错 [英] error when inserting dataframe into MS-SQL with Python

问题描述

我想使用pypyodbc将熊猫数据框插入MS-SQL. 这是我的代码:

I want to insert a pandas dataframe into MS-SQL using pypyodbc. Here is my code:

首先创建一个数据框:

df = pd.DataFrame({'C1': [11, 21, 31],
                    'C2': [12, 22, 32],
                    'C3': [13, 23, 33]})
tablename = tb

然后将数据帧插入MS-SQL

and then insert dataframe into MS-SQL

def insertDFtoSQL(df,tablename):
    con = ppo.connect(r'Driver={SQL Server};
                     'r'Server=se;'
                      r'Database=db;'
                      r'Trusted_Connection=yes;')
    cols = ','.join([k for k in df.dtypes.index])
    params = ','.join('?' * len(df.columns))
    sql = 'INSERT INTO {0} ({1}) VALUES ({2})'.format(tablename, cols, params)
    data = [tuple(x) for x in df.values]
    con.cursor().executemany(sql, data)
    con.commit()
    con.close()

此代码导致显示一条错误消息，提示类型对象不可下标".

this code resulted in an error message that said "type object is not subscriptable".

但是，如果我使用

data1 = [(11,12,13),(22,23,24),(32,33,34)]

将executemany(sql, data)中的数据替换为executemany(sql, data1)

有什么想法吗?

推荐答案

df.values是<class 'numpy.ndarray'>，并且在您这样做时

df.values is a <class 'numpy.ndarray'>, and when you do

data = [tuple(x) for x in df.values]

您将获得一个包含元组的列表，该元组包含类型为<class 'numpy.int64'>的元素. pypyodbc期望元组包含普通" Python类型，因此您需要使用

you get a list of tuples containing elements of type <class 'numpy.int64'>. pypyodbc is expecting the tuples to contain "normal" Python types, so you'll need to use

data = [tuple(int(col) for col in row) for row in df.values]

将数字转换为普通的int值.

to convert the numbers into plain old int values.

这篇关于使用Python将数据帧插入MS-SQL时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！