Bulk insert into Azure SQL with SQLAlchemy not persisting

Question

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:

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.

Any advice would be very much appreciated.

Please, stop using string formatting to create sql queries, you're leaving yourself open to sql injection. Look into "parameterized queries" — roganjosh
It's a bad idea to use string formatting for passing values to queries. Use placeholders and pass values to execute(). It'll remove the need to manually quote etc. — Ilja Everilä
Not a dupe, but my sentiment and an approach is presented here — roganjosh
Fixed my parameterization. Now I'm using pyodbc and I've tried calling cnxn.commit(), executing COMMIT TRANSACTION, etc. still the insert is not persisting — seriestoo2

Jay Gong Jay Gong · Accepted Answer · 2018-04-30T08:55:29

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

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:

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.

Hope it helps you.

Bulk insert into Azure SQL with SQLAlchemy not persisting

1 Answers