OBJECT RELATIONAL MAPPERS (

An object-relational mapper (ORM) is a code library that automates the transfer of data stored in relational database tables into objects that are more commonly used in application code.

ORMs provide a high-level abstraction upon a relational database that allows a developer to write Python code instead of SQL to create, read, update and delete data and schemas in their database. Developers can use the programming language they are comfortable with to work with a database instead of writing SQL statements or stored procedures.

Example:
SQL:

SELECT * FROM USERS WHERE zip_code=94107;

Django ORM:

# obtain everyone in the 94107 zip code and assign to users variable
users = Users.objects.filter(zip_code=94107)

The ability to write Python code instead of SQL can speed up web application development, especially at the beginning of a project.
ORMs also make it theoretically possible to switch an application between various relational databases. For example, a developer could use SQLite for local development and MySQL in production. A production application could be switched from MySQL to PostgreSQL with minimal code modifications.
In practice however, it's best to use the same database for local development as is used in production. Otherwise unexpected errors could hit in production that were not seen in a local development environment. Also, it's rare that a project would switch from one database in production to another one unless there was a pressing reason.
Python ORM libraries are not required for accessing relational databases. In fact, the low-level access is typically provided by another library called a database connector, such as psycopg (for PostgreSQL) or MySQL-python (for MySQL)

Developers can also use ORMs without a web framework, such as when creating a data analysis tool or a batch script without a user interface.

Downsides of ORM

• Impedance mismatch: the difficulties that occur when moving data between relational tables and application objects. The gist is that the way a developer uses objects is different from how data is stored and joined in relational tables.
• Potential for reduced performance: With ORMs, the performance hit comes from the translation of application code into a corresponding SQL statement which may not be tuned properly.
• Shifting complexity from the database into the app code:

Python ORM Implementations

There are numerous ORM implementations written in Python, including:

• SQLAlchemy
• Peewee
• The Django ORM
• PonyORM
• SQLObject
• Tortoise ORM ([source code] (https://github.com/tortoise/tortoise-orm/))

MySQLdb.

MySQLdb is a popular Python interface for connecting to MySQL databases. It is a third-party library that provides a simple and easy-to-use API for working with MySQL databases from Python.
To use MySQLdb in your Python project, you first need to install it. You can install it using pip, the Python package manager, with the following command:

pip install mysqlclient

Once installed, you can use MySQLdb to connect to a MySQL database using the connect() method, which takes a number of parameters including the host, user, password, and database name. For example, to connect to a local MySQL database with a user root and password password, you can use the following code:

import MySQLdb

conn = MySQLdb.connect(host='localhost', user='root', passwd='password', db='mydatabase')

Once you have a connection object, you can use it to execute SQL statements using the execute() method, and retrieve the results using the fetchone() and fetchall() methods. For example, to execute a simple SQL statement to retrieve all rows from a table named mytable, you can use the following code:

cur = conn.cursor()
cur.execute('SELECT * FROM mytable')
rows = cur.fetchall()

Clean up is an easy process. All you need to do is close all open cursors, and close all open database connections. Each cursor and each database connection has a 'close' function, call this for each instance you have created.

import MySQLdb

# Create a database connection
conn = MySQLdb.connect(host='localhost', user='username', passwd='password', db='database_name')

# Create a cursor object
cur = conn.cursor()

# Execute a query
cur.execute('SELECT * FROM mytable')

# Process the query results
rows = cur.fetchall()
for row in rows:
    print(row)

# Close the cursor and database connection
cur.close()
conn.close()

By doing this, you ensure that you're freeing up any resources used by your database connection and cursor. This is especially important if your application needs to connect to a database multiple times or for long periods of time.

SQLAlchemy

The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables.

Version Check

import sqlalchemy
sqlalchemy.__version__ 
1.3.0

Connecting

we will use an in-memory-only SQLite database. To connect we use create_engine():

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=True)

The echo flag is a shortcut to setting up SQLAlchemy logging, which is accomplished via Python’s standard logging module. With it enabled, we’ll see all the generated SQL produced. The return value of create_engine() is an instance of Engine, and it represents the core interface to the database

Declare a Mapping

When using the ORM, the configurational process starts by describing the database tables we’ll be dealing with, and then by defining our own classes which will be mapped to those tables.
Classes mapped using the Declarative system are defined in terms of a base class which maintains a catalog of classes and tables relative to that base - this is known as the declarative base class. Our application will usually have just one instance of this base in a commonly imported module.

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

Now that we have a “base”, we can define any number of mapped classes in terms of it. We will start with just a single table called users, which will store records for the end-users using our application.

from sqlalchemy import Column, Integer, String
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    def __repr__(self):
       return "<User(name='%s', fullname='%s', nickname='%s')>" % (
                            self.name, self.fullname, self.nickname)

SQLAlchemy never makes any assumptions by itself about the table to which a class refers,