Python-based SQLAlchemy operations

Installation

The first prerequisite for using SQLAlchemy in Python is to install the appropriate module, and of course, as a Python advantage, you can open the command line in the menu by holding down shift+ plus the left mouse button under scripts in the Python installation directory.

Use: 1.PIP3 install pymysql 2.PIP3 installs SQLAlchemy two commands to mount the required modules

Use

You need to build the appropriate database before you use it, without having to create a table. Start here:

Related module Import

Import SQLAlchemy  from Import Create_engine  from Import Declarative_base  from Import Column, Integer, String, ForeignKey, UniqueConstraint, Index  from Import Sessionmaker, Relationship  from  Import  And_,or_

Establish a connection

Engine = Create_engine ("mysql+pymysql://root:[email protected]:3306/pythondatabase" )

Engine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/pythondatabase?charset=utf8", echo = True)

#建立连接

#当然推荐上面第二种的方式, one can support Chinese, second, you can see the creation process in Pycharm_console
Base = declarative_base ()# establishes the base class, which is the database

Create a table

1 classUsers (Base):2     __tablename__='User1'  #CREATE table name3 4id = Column (integer,primary_key=true,autoincrement=True)5     #set up self-increment, primary key, etc.6Username = Column (String (), index=true)#Build an index7Password = Column (String (), unique=true)#represents the only
　　

def __repr__ (self):#可加入从而查看表的属性
    return self.username#如

Generate table and delete table

def init_db ():    Base.metadata.create_all (Engine)def  drop_db ():    Base.metadata.drop_all (Engine)# can be directly written, or can be used in two functions, anyway is the same

Change and delete

This need to first set up the session object, so that the further use of delete and change

1 mysession = Sessionmaker (bind=engine)2 session = MySession ()#  Session to provide response to delete and change

Increase (one)

1 ed_user = Users (id=5,username=" Dawn ", password="123456 " )2session.add (ed_user)3 session.commit ()

Increase (multiple)

1 Session.add_all ([2Users (id=6,username="alex1", password='456'),3Users (id=7,username="Alex2", password='789'),4  ])5Session.commit ()#implement the operation, any changes involved need commit ()

Delete

1 session.query (Users). Filter (Users.id > 2). Delete ()2session.commit ()3  # This is about the same as the SQL statement

Change

1Session.query (Users). Filter (Users.id > 2). Update ({the username":"099"})2Session.query (Users). Filter (Users.id > 2). Update ({Users.username:Users.username +"099"}, synchronize_session=False)3Session.query (Users). Filter (Users.id > 2). Update ({"Num": Users.num + 1}, synchronize_session="Evaluate")4Session.commit ()

Inquire

session.query (Users). All ()#Query All forRowinchsession.query (Users). Order_by (users.id):Print(Row)#query sorted by ID forRowinchSession.query (Users). Filter (And_ (users.id==5,users.username=="liming")):    Print(Row) forRowinchSession.query (Users). Filter (Or_ (users.username=="alex1", users.username=="liming")):    Print(Row)#Set Filter CriteriaPrint(Session.query (Users))#in the absence of the. All condition is its native method

Waiting to be added .....

Python-based SQLAlchemy operations