Skip to content

Use parameterized SQL queries

I was having issues getting some queries to work in Winnipeg, since some of the bus stops have apostrophes in the stop names.

For example, one such stop is named St John's Road.

One problem with using string formatting to fill in the WHERE statements is that any tokens that use apostrophes or quotation marks will interrupt the SQL query.

Since the get_gtfs_trip_by_first_and_last_stop method has a clause of:

WHERE name = '{last_stop}'

the SQL engine sees:

WHERE name = 'St. John's Road'

and doesn't know what to do.

This MR introduces parameterized SQL queries to eliminate the problem entirely. SQLite will handle the apostrophes or quotation marks for us.

P.S. I had originally solved this problem by just switching the query to use quotation marks instead of apostrophes. I thought that if I was going to submit a fix back to the project, I should actually solve it using proper techniques. 😊

P. P. S. I don't know what formatting tool you use for your Python code, so I tried to keep indentation and line length similar to what I saw in the file. Please feel free to re-format or edit this however you'd like.

Merge request reports

Loading