Teach everyone to use Python SqlAlchemy

This article is an example of how Python SQLAlchemy is used and shared for everyone's reference, as follows

1. Initializing the connection

From SQLAlchemy import create_enginefrom sqlalchemy.orm Import sessionmakerengine = Create_engine (' mysql:// Pass@localhost/test ' echo=true) dbsession = Sessionmaker (bind=engine) session = Dbsession () ret=session.execute (' desc User ') Print ret# print ret.fetchall () print Ret.first ()

Mysql://root:pass/test
Root is the user name pass password test database
The session is equivalent to the cursor inside the MYSQLDB
First is equivalent to Fetchone
Echo=true will output all of the SQL

2. Create a table

From SQLAlchemy import columnfrom sqlalchemy.types import *from sqlalchemy.ext.declarative import declarative_ Basebasemodel = Declarative_base () from SQLAlchemy import create_enginefrom sqlalchemy.orm Import sessionmakerengine = Create_engine (' mysql://root:hs2bitqlykoruzjbt8sv@localhost/test ') dbsession = Sessionmaker (Bind=engine) class User ( Basemodel):  __tablename__ = ' user1 ' # table name  user_name = column (CHAR (+), primary_key=true)  pwd = column ( VARCHAR (a), default= ' AAA ', nullable=false) Age  = column (SMALLINT (), server_default= ')  accout = column (INT ())  birthday = column (TIMESTAMP ())  Article = column (TEXT ())  height = column (FLOAT ()) def init_db ():  " '  Initialize database  : return:  '  BaseModel.metadata.create_all (engine) def drop_db ():  " Delete all data tables  : return: '  BaseModel.metadata.drop_all (  engine) drop_db () init_db ()

Like the Django Orm. Once the table is created, modifying the user class cannot alter the database structure, only using SQL statements or deleting tables to modify the database structure
Sqlalchemy.types has all the data field types, which equals the uppercase of the SQL type
The default parameter is the time to insert data, sqlalchemy own processing, server_default is to let MySQL processing

3. Add a record

User1=user (user_name= ' lujianxing ', accout=1245678) Session.add (user1) Session.commit ()

It takes a commit to work.

4. Update records
1). Update a single record

query = session.query (user) user = Query.get (' lujianxing11 ') print user.accoutuser.accout= ' 987 ' Session.flush ()

2). Update multiple records

query = Session.query (User) query.filter (user.user_name== ' lujianxing2 '). Update ({user.age: '} ') Query.filter ( user.user_name== ' Lujianxing2 '). Update ({' Age ': ' + '}) Query.filter (user.pwd== ' AAA '). Update ({' Age ': ' 17 '})

5. Deleting records

query = session.query (user) user = Query.get (' lujianxing11 ') session.delete (user) Session.flush ()

6. Enquiry

query = Session.query (User) Print query # displays only SQL statements and does not execute query print query[0] # Execute Query Print query.all () # Execute Query Print query.first () # Execute query for user in query: # Execute query  print user.user_name

If the type of the field is numeric, the type queried is also numeric, not a string
Advanced Point of query:

# Filter User = Query.get (1) # Gets the print query.filter (user.user_name = = 2) # based on the primary key # only the SQL statement is displayed, the query print Query.filter is not executed (User.user_nam E = = ' lujianxing '). All () # Execute Query print query.filter (user.user_name = = ' Lujianxing ', user.accout = 1245678, User.age > 10 ). All () # executes the query print query.filter (user.user_name = = ' lujianxing '). Filter (User.accout = = 1245678). All () print Query.filter ("user_name = ' lujianxing '"). All () # executes the query print query.filter ("user_name = ' lujianxing ' and accout=1245678"). All () # Execute Query query2 = session.query (user.user_name) # The result returned is not the instance of User, but the tuple print query2.all () # Executes the query print Query2.offset (1). Limit (1). All () # equals the limit 1,1# sort print query2.order_by (user.user_name). All () print query2.order_by (' user_name '). All () Print query2.order_by (User.user_name.desc ()). All () print query2.order_by (User.user_name, User.accout.desc ()). All () Print Query2.filter ("user_name = ' lujianxing ' and accout=1245678"). Count () # Aggregate query print session.query (Func.count (' * ')). Select_from (User). Scalar () Print session.query (Func.count (' 1 ')).Select_from (User). Scalar () Print session.query (Func.count (user.id)). Scalar () Print session.query (Func.count (' * ')). Filter (User.ID > 0). Scalar () # filter () contains the User, so you do not need to specify the table print Session.query (Func.count (' * ')). Filter (User.Name = = ' A '). Limit (1). Scalar () = = 1 # The return number of count () can be restricted with limit () Print session.query (Func.sum (user.id)). Scalar () print Session.query (Func.now ()). Scalar () # Func can be followed by any function name, as long as the database supports print session.query (Func.current_timestamp ()). Scalar () Print Session.query (FUNC.MD5 (User.Name)). Filter (User.ID = = 1). Scalar ()

The above is about the use of Python sqlalchemy introduction, I hope that everyone's learning has helped.

