Bulk insert into SQL using system.db.runPrepUpdate

Ignition
1

Hi,

I am using system.net.httpGet() to fetch weather data and writing it to a table in SQL server. Manage to get it working by inserting the data received 1 by 1, but i cannot figure it out to do it with just 1 insert. Any idea how to do it? It is even possible?

The below code works for me:

	import json
	# get the json weather response from the NOAA.
	lat = "38.6524"
	lng = "-121.1896"
	url = "https://api.weather.gov/points/%s,%s" % (lat, lng)
	noaaResponse = system.net.httpGet(url)
	noaaJSON = system.util.jsonDecode(noaaResponse)
	
	# Find the forecast URL.
	properties = noaaJSON["properties"]
	forecastURL = properties["forecast"]
	
	# Get the forecast from NOAA.
	forecastResponse = system.net.httpGet(forecastURL)
	forecastJSON = system.util.jsonDecode(forecastResponse)
	
	# Print out the forecast in a human-readable way.
	periods = forecastJSON["properties"]["periods"]
	
	# Define the SQL query to insert the data into the table
	sql = "INSERT INTO weather_forecast (period_name, temperature, detailed_forecast) VALUES (?, ?, ?)"
	
	#Convert JSON to string if you want to save it in a column later. OPTIONAL.
	#jsonstring = json.dumps(forecastJSON)
	
	#Loop and insert rows 1 by 1
	for data in periods:
	    period_name = data["name"]
	    temperature = str(data["temperature"]) + " °F"
	    detailed_forecast = data["detailedForecast"]
	    args = [period_name, temperature, detailed_forecast]
	    system.db.runPrepUpdate(sql, args, "SQLConnection")
2

Yes, it's certainly possible. You'll want to build your insert string in your for loop and do the runPrepUpdate after the loop. You'll want to be mindful of your SQL transaction limits (ex. 1000 records per insert for SQL Server).

sql = "INSERT INTO weather_forecast (period_name, temperature, detailed_forecast) VALUES " 
for data in periods:
    sql += "(?, ?, ?)," 
    # do other stuff
system.db.runPrepUpdate(sql[:-1], args, "SQLConnection")
1 Like
3

Got this error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 4.

I think "args" values are not being accumulated in the loop and just sending 3 values.

4

Right, you'll need to define args outside of the loop and append each row value. You're creating 1 long insert string, so you need to do the same for the "?" argument values.

2 Likes
5

You can build the args list and then use that length to join your place holder string as needed.

Do know that there is a limit to the number of place holders that JDBC will accept, IIRC its 2500.

sql = "INSERT INTO weather_forecast (period_name, temperature, detailed_forecast) VALUES {}"
args = []
for data in periods:
	args.extend([data['name'], "{} °F".format(data['temperature']),data['detailedForecast']])

system.db.runPrepUpdate(sql.format(','.join(['(?,?,?)'] * len(args)/3)),args,'SQLConnection')

Note the use of extend here as opposed to append. the args needs to be a simple list, not a list of lists.

5 Likes