Forget about sql in 50 lines of python code

https://github.com/jmg/data-layer-generator/blob/master/mapper.py

by jmgutn • 13 years ago

14 21 13 years ago

kirinan• 13 years ago

This doesn't do joins, unions or anything remotely complicated that would allow me to "forget" about sql. SQL is a terribly important thing to know (especially if you work anywhere near a database), and can give more than just python code. Your micro orm is pretty nice, and Im not faulting your work which looks good, just saying add more work to it and its an ORM, and even then you shouldn't forget about sql. Unless this went over my head as a joke, then carry on.

ecopoesis• 13 years ago

Absolutely, emphatically no.

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.

mattmanser• 13 years ago

People are actually starring it! Is this some sort of subtle joke?

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.

jmgutn3• 13 years ago

Of course I'm not saying this code is suitable for production. It's just a toy "ORM". How you could possibly think I'm trying to replace something like sqlalchemy with 50 lines of code? Please guys, I'm not gonna delete the repo. It's just for fun. I'll add a warning about the sql injection problems and explicitly tell that this code is NOT for production. Event when I think that is obvious.

memracom2• 13 years ago

Hey, it's a github repo. If you have discovered an issue with it, then clone the repo, fix the issue, and send in a pull request.

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.

memracom2• 13 years ago

The advice given to programmers using an ORM or a database access library just doesn't work for programmers building one. He can't just "use" bind variables. He has to incorporate the concept into his code.

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.

thinkersilver• 13 years ago

I'm not expecting to get all the bells and whistles of an ORM in the 50 lines so my comment is on the code presented.

You probably want to escape the text.

Use the execute(QUERY[,optional parameters]) syntax.

http://stackoverflow.com/questions/902408/how-to-use-variabl...

Keyframe2• 13 years ago

On a side note, is SQL the only declarative language widely in use?

beagle3• 13 years ago

Excel is functional, and in a way declarative.

Note, I'm not talking about VBA - I am talking about Excel's formulas

Keyframe2• 13 years ago

Good catch!

daGrevis• 13 years ago

I have always wondered could something like this could be implemented as database backend for Django next to SQLite3, MySQL and Postgres backends. The reason for it would be ability to run tests that uses database w/o actually using database (because databases are freaking slow even when they are in memory).

Aqueous• 13 years ago

The last query I wrote had 6 or 7 joins in it, a union, and calculated the frequency of answer responses for a worldwide survey (tens of thousands of respondents) in 0.04 seconds with a 1 million row table. You can accomplish an awful lot of business logic in a single SQL query, which makes SQL tremendously useful thing to know. On the other hand it is tedious to tweak an object mapper call to give you the result that you want that efficiently.

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.

buremba• 13 years ago

It's most likely a troll.

mattgreenrocks• 13 years ago

Micro-ORMs were en vogue for a bit in .NET; I'm not sure about their status at present.

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.

JulianWasTaken• 13 years ago

For anyone looking for this actually done properly, there's already the SQLAlchemy query-builder layer.

fein• 13 years ago

ORM's are for basic CRUD crap.

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.

jmgutn3• 13 years ago

Totally agree. This is the reason I wrote this tiny toy "ORM" just for CRUD.

cjauvin• 13 years ago

I agree with all the comments saying that this is dangerous and not a good idea in general. However I've been doing (and using) something related (more in the spirit of Clojure HoneySQL, as I discovered recently), where the idea is to ease the interop of plain data structures and SQL in a pythonic way:

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.

vinceguidry• 13 years ago

I fell under the spell of ORM, and used it until I had to generate an xml product feed from a large, complex CRM database. It didn't take me long to throw out Sequel and write a gigantic SQL query for huge performance gains. You can do joins in Sequel, but it's a pain in the neck.

Forget about sql in 50 lines of python code

Related Stories