Build SQL queries in Python

Question

Are there any python packages that help generating SQL queries from variables and classes?

For example, instead of writing create query manually, the developer will create a create table (as an object maybe), with desired columns in a list for instance. Then the object will return a string that will be used as a query. It would be a plus if such package can support multiple language syntax (SQLite, Oracle, MySQL, ...)

Answer 1

The standard python MySQLdb package will do the right things about quoting variables if it's given a chance. If you're worried about SQL injection attacks.

c.executemany(
      """INSERT INTO breakfast (name, spam, eggs, sausage, price)
      VALUES (%s, %s, %s, %s, %s)""",
      [
      ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
      ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
      ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
      ] )

Otherwise you should probably be looking at any number of python web frameworks - Django, etc. to abstract the database.

Answer 2

Probably the best Object-Relational mapper package for Python today is the popular SqlAlchemy.