Tutorial on using SQLAlchemy to operate databases in Python Django framework, djangosqlalchemy

Tutorial on using SQLAlchemy to operate databases in Python Django framework, djangosqlalchemy

What is zero and SQLAlchemy?
SQLAlchemy's official website contains the following text:

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives
Application developers the full power and flexibility of SQL.
SQLAlchemy is a very powerful ORM and database tool, but its huge documentation and complex functions are always daunting to many people. Django's ORM makes many people feel simple and practical.

In fact, SQLAlchemy is not that complicated. using some of its advanced functions is actually not much more complicated than using Django ORM, its rich functions allow you to handle more complex problems.

The main purpose of writing this article is:

• By comparing the main usage methods of SQLAlchemy ORM and Django ORM, it is as simple and intuitive as possible to allow Django users to quickly understand and get started with SQLAlchemy, a powerful tool.
• The detailed technical details of SQLAlchemy are not involved, including the specific working principles of the Engine Connection Pool and Session.

Compared with the built-in ORM of Django, SQLAlchemy has several obvious advantages:

• It can be used independently. Any Python project can use it to operate databases.
• Compared with the direct use of the original DBAPI, it provides a wealth of features: Connection Pool, auto-map, and so on.
• Provides a lower-level SQL abstract language. You can use SQLAlchemy to solve problems solved by using the original SQL.
• Next we will compare the daily database operations with Django ORM and SQLAlchemy.

The SQLAlchemy version used in this article is 0.9.8

1. Django VS SQLAlchemy

SQLAlchemy installation:

 wget http://peak.telecommunity.com/dist/ez_setup.py python ez_setup.py sudo easy_install sqlalchemy sudo easy_install ipython

1. Create a data table

First, we need to create several tables first.

(1) Django

In Django, to create a table, define your data type in models. py:

from django.db import modelsclass Game(models.Model): ... ...class GameCompany(models.Model): ... ...

This article is intended for experienced Django users, so detailed definition code is not written here. After defining the Model, we also need to set the database address to be connected at DATABASES in settings. py. Finally, use syncdb to create a database table.

(2) SQLAlchemy

In SQLAlchemy, the process of defining the table structure is similar to that of Django:

From sqlalchemy import create_enginefrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, Datefrom sqlalchemy. orm import relationship, backrefBase = declarative_base () # define the table structure class GameCompany (Base): _ tablename _ = 'game _ company 'id = Column (Integer, primary_key = True) name = Column (String (200), nullable = False) country = Column (String (50 )) Class Game (Base): _ tablename _ = 'game' id = Column (Integer, primary_key = True) company_id = Column (Integer, ForeignKey ('game _ company. id'), index = True) category = Column (String (10) name = Column (String (200), nullable = False) release_date = Column (Date) # unlike Django, foreign keys must be explicitly defined, for details, see this document. # The relation here provides configurable options for lazy to load foreign key content: company = relationship ('gamecompany', backref = backref ('games ')) # define the database eng to be used Ine = create_engine ('mysql: // root: root @ localhost: 5379/sqlalchemy_tutorial? Charset = utf8') # Call create_all to create the table structure. Existing tables are ignored. Base. metadata. create_all (engine)

2. Insert some data

Next, we will insert some data into the table.

(1) Django

The Common Data insertion method in Django is to use. save.

Nintendo = GameCompany (name = "nintendo", country = "Japan") nintendo. save () game1 = Game (company = nintendo, category = "ACT", name = "Super Mario Bros", release_date = '2017-10-18 ') game1.save () # Or use createGame. objects. create (......)

(2) SQLAlchemy

In SQLAlchemy ORM, there is a very critical object session. All data operations are performed through session. Therefore, before inserting data, we must initialize a session:

from sqlalchemy.orm import sessionmakerSession = sessionmaker(bind=engine)session = Session()

The method for inserting data is similar to that of Django:

# Add data nintendo = GameCompany (name = "Nintendo", country = "Japan") capcom = GameCompany (name = "Capcom", country = "Japan ") game1 = Game (company = nintendo, category = "ACT", name = "Super Mario Bros", release_date = '2017-10-18 ') game2 = Game (company = capcom, category = "ACT", name = "dedevil May Cry 3: Dante's Awakening", release_date = "",) game3 = Game (company = nintendo, category = "RPG", name = "Mario & Luigi: Dream Team", release_date = "2013-08-11",) # Use add_all to associate these objects with sessions. add_all ([nintendo, capcom, game1, game2]) # In autocommit mode, do not forget to call commit to write data to the database session. commit ()

In addition to commit, the session also has rollback () and other methods. You can simply regard the session object as a transaction, so when you modify the content, you need to call the session. commit () to submit these changes.

Go to the document to learn more about session-related content: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html

Ii. Common Operations

1. Simple Query

(1) batch query

# -- Django -- Game. objects. filter (category = "RPG") # -- SQLAlchemy -- # using filter_by is a session that is close to django ORM. query (Game ). filter_by (category = "RPG") session. query (Game ). filter (Game. category = "RPG ")

(2) query a single object

# -- Django --Game.objects.get(name="Super Mario Bros")# -- SQLAlchemy --session.query(Game).filter_by(name="Super Mario Bros").one()# `get_objects_or_None()`session.query(Game).filter_by(name="Super Mario Bros").scalar()

In Django, all types of> and <are implemented by appending "_ gt" and "_ lt" after the field name. Such queries in SQLAlchemy are more intuitive.

# -- Django -- Game. objects. filter (release_date _ gte = '1970-01-01 ') # reverse Game. objects. exclude (release_date _ gte = '1970-01-01 ') # -- SQLAlchemy -- session. query (Game ). filter (Game. release_date> = '2017-01-01 '). count () # reverse use ~ Operator session. query (Game). filter (~ Game. release_date> = '2017-01-01 '). count () is queried by a combination of Foreign keys # -- Django -- Game. objecs. filter (company _ name = "Nintendo") # -- SQLAlchemy -- session. query (Game ). join (GameCompany ). filter (GameCompany. name = "Nintendo ")

2. Multi-condition or query

# -- Django --from django.db.models import QGame.objects.filter(Q(category="RPG") | Q(category="ACT"))# -- SQLAlchemy --from sqlalchemy import or_session.query(Game).filter(or_(Game.category == "RPG", Game.category == "ACT"))session.query(Game).filter((Game.category == "RPG") | (Game.category == "ACT"))

(1) in Query

# -- Django --Game.objects.filter(category__in=["GAL", "ACT"])# -- SQLAlchemy --session.query(Game).filter(Game.category.in_(["GAL", "ACT"]))

(2) like Query

# -- Django --Game.objects.filter(name__contains="Mario")# -- SQLAlchemy --session.query(Game.name.contains('Mario'))

3. Count

Summary:

# -- Django -- Game. objects. filter (category = "RPG "). count () # -- SQLAlchemy -- session. query (Game ). filter_by (category = "RPG "). count () group statistics # -- Django -- from django. db. models import CountGame. objects. values_list ('category '). annotate (Count ('pk ')). order_by () # -- SQLAlchemy -- from sqlalchemy import funcsession. query (Game. category, func. count (Game. category )). group_by (Game. category ). all ()

4. sorting results

Sort the query results:

# -- Django -- Game. objects. all (). order_by ('release _ date') Game. objects. all (). order_by ('-release_date') # multi-field sorting Game. objects. all (). order_by ('-release_date', 'category ') # -- SQLAlchemy -- session. query (Game ). order_by (Game. release_date) session. query (Game ). order_by (Game. release_date.desc () # multi-field sorting session. query (Game ). order_by (Game. release_date.desc (), Game. category)

5. modify data

# -- Django --game = Game.objects.get(pk=1)game.name = 'Super Mario Brothers'game.save()# -- SQLAlchemy --game = session.query(Game).get(1)game.name = 'Super Mario Brothers'session.commit()

6. Batch Modification

# -- Django --Game.objects.filter(category="RPG").update(category="ARPG")# -- SQLAlchemy --session.query(Game).filter_by(category="RPG").update({"category": "ARPG"})

7. Batch Delete

# -- Django --Game.objects.filter(category="ARPG").delete()# -- SQLAlchemy --session.query(Game).filter_by(category="ARPG").delete()

Iii. SQLAlchemy other functions worth attention
The above briefly lists the usage comparison between SQLAlchemy ORM and Django ORM. SQLAlchemy also provides some useful functions, such as Automap~

Assume that you have a Django project that creates a large number of models through ORM. What should I do if a new project needs to operate these tables? Copy these Models? Using the original DB-API plus SQL?

In fact, using Automap of SQLAlchemy makes your work very convenient. You only need to connect to the old database in the new project, and then configure Automap slightly, you can use the ORM of SQLAlchemy to operate the tables created through other systems.

Like this:

From sqlalchemy. ext. automap import automap_basefrom sqlalchemy. orm import Sessionfrom sqlalchemy import create_engineBase = automap_base () engine = create_engine ("sqlite: // mydatabase. db ") Base. prepare (engine, reflect = True) # user and address indicate that they can be mapped to User and Address class User = Base respectively through such statements. classes. userAddress = Base. classes. address

For more information, see the detailed documentation: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html

Appendix: demo of Django and SQLAlchemy
For example, the following gumi/db. py code, in which gumi creates the Django project name and packages the unique database connection used in the project as the py call.

# -*- coding: utf-8 -*- from django.conf import settings from django.core import signals from django.dispatch import dispatcher import sqlalchemy from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.engine.url import URL  __all__ = ['Session', 'metadata']  def create_engine():  url = URL(drivername=settings.DATABASE_ENGINE,     database=settings.DATABASE_NAME,     username=settings.DATABASE_USER,     password=settings.DATABASE_PASSWORD,     host=settings.DATABASE_HOST,     port=settings.DATABASE_PORT or None,     query = getattr(settings, 'DATABASE_OPTIONS', {})     )   options = getattr(settings, 'SQLALCHEMY_OPTIONS', {})  engine = sqlalchemy.create_engine(url, **options)  return engine  def end_request(signal, sender):  Session.remove()  dispatcher.connect(receiver=end_request,      signal=signals.request_finished)  metadata = sqlalchemy.MetaData()  Session = scoped_session(sessionmaker(autoflush=True,           transactional=True,           bind=create_engine())) 

Module code

from sqlalchemy.orm import * from gumi.db import Session, metadata some_table = Table('some_table', metadata,        Column('id', Integer, primary_key=True),        Column('some_value', String(100), nullable=False,        mysql_engine='InnoDB',        ) class SomeObject(object):  pass mapper(SomeObject, some_table) 

View code

import django.newforms as forms from gumi.db import Session  class SomeForm(forms.Form):   # newform   pass  def some_action(req):   if req.method != "POST":    form = SomeForm()   else:    form = SomeForm(req.POST)    if form.is_valid():      data = form.clean()      obj = SomeObject()      obj.some_param = data['a']      obj.another_param = data['b']      Session.save(obj)      Session.commit()      return HttpResponseRedirect('/')   return render_to_response('some/template.html')