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 ([ http://docs.sqlalchemy.org/en/latest/orm/tutorial.html])
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')>" % ( self.name, self.id) class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True, nullable=True) email_address = Column(String(50)) userid = Column(Integer,ForeignKey('test.id')) #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') jack.addresses=[Address(email_address='111111'),Address(email_address='222222')] 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 print(jack) print(jack.addresses) #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 try: cursor = connection.cursor() #No parameters cursor.callproc('find_test') for i in cursor.stored_results(): #The results are all stored in the stored results() function print(i.fetchall()) cursor.close() cursor = connection.cursor() # With parameters args=['guhongye',0]#Output parameters can be occupied by 0 result = cursor.callproc('find_test1',args=args) print(result) cursor.close() finally: connection.close()
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 env.py 17 as follows import os import sys root = os.path.dirname(__file__)+'/../' # Navigate to the project root sys.path.append(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