Basic knowledge of python operation database

How does python access the database? Through the database interface DB-API, it is a standard to clarify a series of required objects and database access mechanisms. It can provide consistent access for different database adapters and underlying database systems.

In order to access the database, python has developed many adapters for connecting to the database.
For example, mysql connector Python and pymysql can be used in Python 3.

There are two ways to interact with the database, one is simple and direct original sql statement, the other is ORM (if you prefer to operate python objects, this is a better choice)

1. Native sql

  import adapter
  Connect method connects to the database and returns the connect object
  The cursor object that generated the connect
  cur = connect.cursor()
  Call method to add, delete, modify and query database
  callpro execute common
  Close connection


Squalchemy is a popular Python ORM, which supports python3 +, and the installation method is very simple. Best tutorial ([])

I try to say its basic usage in the simplest language.

orm is the mapping object for establishing database. Through the built-in declarative base method of sqlalchemy, the mapping with database tables is completed.

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import String, Integer, Column
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship,sessionmaker
#First, connect to the database, echo=True, so that you can view the generated native SQL when executing in the command window
engine = create_engine('mysql+mysqlconnector://user:psw@ip/dbname',echo=True)

Base = declarative_base()
#Inherit Base and map users table to User object
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, nullable=True)
    name = Column(String(20))
    newpassword = Column(String(20))
    getpassword = Column(String(20))
    addresses = relationship("Address", back_populates="user")#Establish relationship with Address
    def __repr__(self):  #Command window debugging
        return "<User(name='%s', id='%s')>" % (

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True, nullable=True)
    email_address = Column(String(50))
    userid = Column(Integer,ForeignKey('')) #Build foreign key
    user = relationship("User", back_populates="addresses")
    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

   jack = User(name='ttt',newpassword='123',getpassword='123')
    Session = sessionmaker(bind=engine) #Establish session. The ORM's "handle" to the database is the Session

    session = Session()
    session.add(jack) #Add object
    session.commit() #The database will not change until it is committed. Need flush
    jack = session.query(User).filter_by(name='ttt').one() #query
    session.query(User).join(Address) #Cross query join for more information, see that article
#How to use stored procedures
#Create stored procedure details, use delimiter $$to change the default when using the command line; end execution
#Calling Stored Procedures
    connection = engine.raw_connection() #Get native connection
        cursor = connection.cursor()
        #No parameters
        for i in cursor.stored_results(): #The results are all stored in the stored results() function
        cursor = connection.cursor()
        # With parameters
        args=['guhongye',0]#Output parameters can be occupied by 0
        result = cursor.callproc('find_test1',args=args)


alembic is a concise tutorial. The following article is very detailed

1.alembic init YOUR_ALEMBIC_DIR
2.alembic revision -m "create account table"
#Automatically update line 17 as follows
import os
import sys
root = os.path.dirname(__file__)+'/../' # Navigate to the project root
from learnsqlalchemy import Base
target_metadata = Base.metadata
#It can be executed as follows
  alembic revision  --autogenerate -m "create account table"
3.alembic upgrade head
4.alembic downgrade Version No

Tags: Database Session Python SQL

Posted on Sun, 03 May 2020 00:47:37 -0700 by harshilshah