-1

I am a newbie Python developer but a proficient relational database developer so I don't want to use an ORM I want to use a wrapper. The main features I want are

  1. Same interface for multiple DBMS, hides differences, in particular mariaDB, SQLServer and SQLite.
  2. Handles DML (Select, Insert, Update, Delete).
  3. Returns result set in an efficient pagination-friendly object.
  4. Optionally can extend the classes involved with my own methods.
  5. Simple direct access to the database tables.
  6. Is not SQLAlchemy because it is an ORM :).

When I look there are apparently lots of wrappers but they all look old and tired, perhaps because everyone likes ORMs.

Incidentally, I am using Flask.

I am thinking that maybe Pandas dataframes may do what I need, at least for the result set?

Any advice? Should I just roll my own?

0

Almost every Python database interface library will implement the Python DB-API (https://www.python.org/dev/peps/pep-0249/), so they will all have fundamentally the same interface for different databases. Useful versions of these libraries are:

  • psycopg2 for Postgres
  • pyodbc for SQL Server
  • pymysql for MariaDB/MySQL
  • fdb for Firebird
  • Python's native SQLite library for SQLite.

There are some minor differences between them, such as how, or whether, they return the number of rows affected by a DML statement, but these libraries provide basically identical interoperability across DBMSs -- and you can easily create a class and subclasses to further standardize the interfaces if you want to implement higher-level functions than provided by the DB-API.

  • Is it possible to wrap these in a DB-API compliant user-defined class and pass in the URI which has the database type in __init__(). I think it would be, but may not be worth the effort? – Mark Kortink Jun 24 at 2:34
  • @MarkKortink - The connect() methods differ in the type and format of the arguments they take, which are dependent on the DBMS and what type of user credentials are needed, but it is possible to create a wrapper class that takes a complete DBMS-specific connection string, or create subclasses that take a set of specific arguments (server, database, user, password) and create the appropriate connection strings. Note that in some cases (e.g., SQL Server) the connection string is also dependent on which version of the database driver is installed. – rd_nielsen Jun 24 at 2:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.