Python-based SQLAlchemy operations

Source: Internet
Author: User


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


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 ()
1 session.query (Users). Filter ( > 2). Delete ()2session.commit ()3  # This is about the same as the SQL statement

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

session.query (Users). All ()#Query All forRowinchsession.query (Users). Order_by ( sorted by ID forRowinchSession.query (Users). Filter (And_ (,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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.