使用SQLAlchemy批量插入Azure SQL不会持久化

I'm trying to use SQLAlchemy in Python to execute a bulk insert in my Azure SQL database (from blob storage). The external data source is working properly and I can run the query from SQL Server Management Studio. When I try to query from python:

我正在尝试在Python中使用SQLAlchemy在我的Azure SQL数据库中执行批量插入(来自blob存储)。外部数据源工作正常,我可以从SQL Server Management Studio运行查询。当我尝试从python查询时:

query = '''

    BULK INSERT mytable FROM '%s.csv' WITH (DATA_SOURCE = 'blobStorage',
    FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW=2) 
''' % report_date

insert = connection.execute(query)

the query runs without errors. Then:

查询运行没有错误。然后:

sel = connection.execute("SELECT count(*) FROM mytable where DATE='%s'" % report_date)

returns the number of new rows.

返回新行的数量。

(36026,)

I can select the new rows and see the data all looks correct.

我可以选择新行,看看数据看起来都正确。

However, moving back into SQL Server Management Studio, trying to select the new rows returns nothing. The new rows aren't in the table and when I restart my Python script and try to select again, the rows are gone.

但是,回到SQL Server Management Studio,尝试选择新行不会返回任何内容。新行不在表中,当我重新启动Python脚本并尝试再次选择时,行就消失了。

Any advice would be very much appreciated.

任何建议将非常感谢。

1 个解决方案

#1

I used following python code to import the data in the csv file into sql database successfully.

我使用以下python代码将csv文件中的数据成功导入sql数据库。

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=***.database.windows.net;DATABASE=***;UID=***;PWD=***')

cursor = cnxn.cursor()

sql = """
BULK INSERT jaygong.dbo.Student
FROM 'insert.csv' 
WITH (
    DATA_SOURCE = 'j',
    FIELDTERMINATOR=',',
    ROWTERMINATOR='\n'
);
"""

cursor.execute(sql)
cnxn.commit()
cursor.close()
cnxn.close()

csv file:

create external data source sql:

创建外部数据源sql:

CREATE EXTERNAL DATA SOURCE j  
    WITH (   
        TYPE = BLOB_STORAGE,  
        LOCATION = 'https://***.blob.core.windows.net/test1'
    )

Insert result：

In addition, I provide you with a workaround that you could use Azure Data Factory. It supports setting input and output data source. You could use it to bulk import data from Azure Storage into Azure SQL Database without any code.

此外,我为您提供了一种可以使用Azure Data Factory的解决方法。它支持设置输入和输出数据源。您可以使用它将数据从Azure存储批量导入Azure SQL数据库而无需任何代码。

Hope it helps you.

希望它能帮到你。

#1