我尝试使用pymssql
将数据插入到SQL Server表中。我的代码如下:
def main():
out = get_sql()
print("%s Batches to be exported" %(len(out)))
with concurrent.futures.ProcessPoolExecutor(max_workers=4) as executor:
for batch, i in enumerate(executor.map(scoring_and_loading, out)):
try:
conn = pymssql.connect(server="SQLDev02",database="DEVDB", user='', password='')
curr = conn.cursor()
query = "INSERT INTO [dbo].[STG_CONTACTABILITY_SCORE] VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
sql_data = list(map(tuple,i.values))
curr.executemany(query, sql_data)
conn.commit()
print("Batch %s exported" %(batch))
except Exception as e:
print(e)
字符串STG_CONTACTABILITY_SCORE
的定义如下:
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[STG_CONTACTABILITY_SCORE](
[RecordType] [varchar](30) NULL,
[ContactName] [varchar](100) NULL,
[AccountName] [varchar](100) NULL,
[ContactabilityScore] [int] NULL,
[PreferredPhoneNumber] [varchar](15) NULL,
[PreferredCallerId] [varchar](15) NULL,
[DoubleDialRecommended] [varchar](1) NULL,
[DayofTheWeek] [varchar](15) NULL,
[CallBlockStartTime] [varchar](15) NULL,
[CallBlockEndTime] [varchar](15) NULL,
[Season] [varchar](15) NULL,
[CallDuration] [varchar](15) NULL,
[DialNumber] [varchar](20) NULL,
[Cumulative#ofAttempts] [int] NULL,
[LastUpdatedDate] [date] NULL,
[LoadDate] [date] NULL,
[LoadStatus] [varchar](30) NULL,
[LoadNumber] [int] NULL,
[ContactId] [varchar](50) NULL,
[TimeZone] [varchar](200) NULL,
[BatchNumber] [varchar](50) NULL,
[AccountId] [varchar](20) NULL
) ON [PRIMARY]
GO
型
关于我试图插入到表中的 Dataframe 的信息如下所示:
<class 'pandas.core.frame.DataFrame'>
Int64Index: 9800 entries, 0 to 9799
Data columns (total 22 columns):
RecordType 9800 non-null object
ContactName 9800 non-null object
AccountName 9800 non-null object
ContactabilityScore 9800 non-null float64
PreferredPhoneNumber 9800 non-null object
PreferredCallerId 0 non-null object
DoubleDialRecommended 0 non-null object
DayofTheWeek 9800 non-null object
CallBlockStartTime 9800 non-null object
CallBlockEndTime 9800 non-null object
Season 9800 non-null object
CallDuration 0 non-null object
DialNumber 0 non-null object
Cumulative#ofAttempts 0 non-null object
LastUpdatedDate 9800 non-null object
LoadDate 9800 non-null object
LoadStatus 9800 non-null object
LoadNumber 0 non-null object
ContactId 9800 non-null object
TimeZone 9800 non-null object
BatchNumber 0 non-null object
AccountId 9800 non-null object
dtypes: float64(1), object(21)
型
我检查了AccountName
或ContactName
中是否有任何长度大于100的值,但没有找到。我不明白我做错了什么。我将非常感谢您的帮助。提前感谢。
1条答案
按热度按时间omvjsjqw1#
请检查你的表模式。在我的例子中,我定义了列abc varchar(55),值长度为125。确保你定义的数据长度,数据类型。