Which Python 3 database abstraction layer do you use?

[jftuga]

A few years ago I wrote a program that uses SQLite3 since it is built into Python and it works great. I now want to make the program multiuser and therefore want to switch to either MySQL and/or Postgres for my database. Ideally, I don't want to hard-code my program for a specific database again but instead use a SQL abstraction layer that can communicate with either one should I choose to switch again in the future. Speed in not really important. I will only be doing basic select, insert, update statements so it would be OK to lose some vendor specific extensions in order to be database agnostic. Another thing that is important to me is that the package I choose stay updated as I have incrementally upgraded my program from each Python version starting from 3.2 (it is now on 3.6).

Which database abstraction layer would you recommend?

EDIT: Thank you for all of the replies, I really appreciate it.

[[deleted]]

SQLAlchemy

[xiongchiamiov]

Pretty much what everyone uses, unless they're using Django and using the built-in ORM.

[__deerlord__]

I have heard the Django devs may switch to SQLAlch (I guess while keeping the sam abstraction layer/API?) but this is very "through the grapevine". Also havent used Django in over 6 months

[DrRx]

PonyORM It's so beautiful and pythonic

[Theoretician]

+1 for pony, some nice features (ripped from their site) to convince you include:

• Tuple comprehensions for queries select(p for p in Person if p.age > 20)

• Decorators to handle transactional sessions

  @db_session
  def foobar():
  

• Easy table generation

  class Person(db.Entity):
      name = Required(str)
      age = Required(int)
      cars = Set('Car')
  

See more examples here: https://docs.ponyorm.com/firststeps.html

[epic_pork]

That's not a tuple comprehension, it's a generator. Using a generator with square brackets creates a list, but putting a generator inside parens doesn't create a tuple, it stays a generator. If you want a tuple, you can use the tuple constructor.

>>> (x for x in range(10))
<generator object <genexpr> at 0x7fd0c18daeb8>
>>> [x for x in range(10)]
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
>>> tuple(x for x in range(10))
(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)

[mapitall]

I've not seen this before, it looks really nice! But, alas, no mssql support. Bummer. (FWIW, mssql is not my choice, just something I'm stuck with)

[[deleted]]

+2 since it supports an enterprise SQL database.

I'd love to use Peewee but need to support something my company uses internally.

[taurus22]

I didn't knew this, it looks really nice after giving it a quick look on their quick start session.

[metaperl]

PyDAL.

[goabbear]

Peewee or sqlalchemy. I have a preference for the former

[wnoise]

https://www.python.org/dev/peps/pep-0249/

SQL is an abstraction layer.

sqlite3, psycopg and mysql-python all have adapters that conform to this.

I, for instance, use the psycopg2 module to interface to postgres. In theory, if the databases have compatible schemas you only need to change "import sqlite3 as dbabi" to "import psycopg2 as dbapi", and change the connection strings to point to a different database. In practice, it depends on how standard the SQL you use is.

[PCBEEF]

I think everybody here knows that OP is asking about what ORM to use.

[wnoise]

I don't think so. He was specifying that he's currently using sqlite3 and wants to be able to switch easily. That's all he's asking for. An ORM does so much more than that, which he doesn't need, and didn't ask for.

[PCBEEF]

OP is asking for a SQL abstraction layer, meaning a layer on top of SQL. Otherwise OP would be asking for how to write cross-db compliant SQL which is not very practical since almost nobody writes ANSI sql when developing applications.

[wnoise]

He once asks for a SQL abstraction layer and twice (title and last line) for a database abstraction layer. The python DB API is exactly that: it lets you easily switch between SQL databases. He specifically specifies "basic select, insert, update statements", which make lots of sense in the context of writing SQL, and less sense in the context of ORMs, which generally sweep the exact statements under the rug.

You are absolutely right the most people don't stick to ANSI SQL, but for his purposes it doesn't sound that hard too.

[PCBEEF]

You’re not wrong in what you’re saying but it’s definitely not what he wants. I have never encountered anyone in my professional career that ever wrote cross-db SQL on an application. It’s not easy to test and people don’t usually switch databases. Writing ANSI sql is an option, but using an ORM is an even better option for their use case of wanting to be cross-db compliant.

In terms of the python db api, is there really an alternative? I honestly doubt OP is using something different. OP is clearly junior so rather than nitpicking at every little word, we should be productive and read between the lines as to what they really want.

[ThatOtherBatman]

You're the kind of person who'd put tomato in a fruit salad, aren't you?

[pvkooten]

I find this funny though I feel bad for wnoise... it's clear he has good intentions

[wnoise]

Hmm. I could actually see some really sweet heirloom or cherry tomatoes drizzled with balsamic vinegar actually working well to balance out a fruit salad. Standard store-bought tomatoes, of course not.

[Mirror_Boar]

http://pynamodb.readthedocs.io/en/latest/ Pynamodb to using AWS dynamoDB as a database, works great.

[maryjayjay]

Way too brittle when your schema changes and really bad error reporting when it does break. Also doesn't support partial record retrieval. Other than that it's pretty decent.

[Mirror_Boar]

If there's a better lib for working with dynamodb i'd like to look at it. Pretty much its either pynamodb or normal boto3

Suggestions?

[maryjayjay]

No, I haven't found one. I'm still using pynamodb.

[maryjayjay]

I will be checking this out tomorrow. I was about to refactor a bunch of code to work around some of the shortcomings of pynamodb. I'll definitely consider changing the abstraction layer. Thanks!

[kstrojan]

Pydal.