Python replacement for SQL bcp.exe

The goal is to load a csv file into an Azure SQL database from Python directly, that is, not by calling bcp.exe. The csv files will have the same number of fields as do the destination tables. It'd be nice to not have to create the format file bcp.exe requires (xml for +-400 fields for each of 16 separate tables).

Following the Pythonic approach, try to insert the data and ask SQL Server to throw an exception if there is a type mismatch, or other.

Solution

If you don't want use bcp cammand to import the csv file, you can using Python pandas library.

Here's the example that I import a no header 'test9.csv' file on my computer to Azure SQL database.

Csv file:

Python code example:

import pandas as pd
import sqlalchemy
import urllib
import pyodbc

# set up connection to database (with username/pw if needed)
params = urllib.parse.quote_plus("Driver={ODBC Driver 17 for SQL Server};Server=tcp:***.database.windows.net,1433;Database=Mydatabase;Uid=***@***;Pwd=***;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;")

engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

# read csv data to dataframe with pandas
# datatypes will be assumed
# pandas is smart but you can specify datatypes with the `dtype` parameter
df = pd.read_csv (r'C:\Users\leony\Desktop\test9.csv',header=None,names = ['id', 'name', 'age'])

# write to sql table... pandas will use default column names and dtypes
df.to_sql('test9',engine,if_exists='append',index=False)

# add 'dtype' parameter to specify datatypes if needed; dtype={'column1':VARCHAR(255), 'column2':DateTime})

Notice:

get the connect string on Portal.
UID format is like [username]@[servername].

Run this scripts and it works:

Please reference these documents:

Hope this helps.