BritishGeologicalSurvey / etlhelper

ETL Helper is a Python ETL library to simplify data transfer into and out of databases.
https://britishgeologicalsurvey.github.io/etlhelper/
GNU Lesser General Public License v3.0
105 stars 25 forks source link

Add `select_sql_suffix` and `insert_sql_suffix` to copy_rows and load modules #119

Closed volcan01010 closed 2 years ago

volcan01010 commented 2 years ago

Summary

As an ETLHelper user I want to be able to add WHERE clauses to copy_table_rows so that I have more flexibility while still not having to write full queries

Description

copy_table_rows runs a "SELECT * FROM table" query, and load runs an "INSERT INTO table (...) VALUES (...)" query. In the first case, it would be nice to be able to add a WHERE or ORDER BY or LIMIT to the query. In the second case it would be nice to add "ON CONFLICT DO NOTHING" or something similar.

Adding parameters for select_sql_suffix and execute_sql_suffix to the respective functions that appended whatever text was given would be a simple way to allow this.

Acceptance criteria

volcan01010 commented 2 years ago

On reflection, I have decided against this. Although this code is unlikely to be used to generate queries from user-derived input, allowing arbitrary text strings to be included into SQL statements isn't good practice.

Instead, commit 11c4b59d354b12c0424c7293e6def7a9c5e5d6fb makes the generate_insert_sql function more accessible. Users can call it directly to provide a base query.