Building SQL strings from user data is a terrible idea. You will have SQL injections. You will compromise your database. I'm sure someone else will add the obligatory link to the Johnny Droptables xkcd.
Just learn SQL. It is just not the hard. And please, please use bind variables.
It is unfortunate that the Python DB-API evolved in the way that it did. It is only an API doc, and therefore every db library has to reimplement much of the same functionality, like the string escaping that prevents SQL injection attacks.
It would do a lot of good for a simple thin mapper like this to be more widely used so that more people can see what is involved in making things secure, as opposed to just piggybacking on a library that does everything for you.
Just to emphatically underline ecopoesis' point, this code is completely insecure, open to the most basic SQL injection attacks.
Op you need to delete the repo asap.
Yes, it does the simple form of variable substitution that opens the door to SQL injection attacks, but using a .execute method with ? in the SQL for variable substition is not the real solution. The real solution is what happens inside the .execute methods. There is no reason why that same code could not be incorporated in this mapper, and still maintain the goal of a minimal data access layer with no magic.
You can get some ideas in how to improve the code here https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/curso... starting around line 91.
You probably want to escape the text.
Use the execute(QUERY[,optional parameters]) syntax.
http://stackoverflow.com/questions/902408/how-to-use-variabl...
This is useful for abstracting away some routine SQL queries, but I think people think simple selects and inserts are more routine than they are actually are.. If you structure your data layer in a way that takes advantage of the power of the RDMBS I find that the only query here is truly routine is insertion on a single row...
We already have a language for expressing relational database queries - SQL. Every time you abstract away this language you risk reducing its expressiveness.
SQL is for queries that matter. My ORM usage is almost entirely limited to: get this record, change these values, save it. Anything involving complex joins/ unions gets thrown to raw SQL. Better yet, stored functions on the DB side. Postgres is awesome for this.
https://github.com/cjauvin/little_pger
I always got the nagging feeling that I should rather use SQLAlchemy, which probably solves the problem in a more elegant and general way, but this little thing has been working great for me.
All SQL DSLs seem a bit over-the-top to me; you're layering a DSL atop another DSL (SQL), forcing you to understand the former intimately, along with knowing the latter well enough to write decent queries.
