Applying SQL and SQLAlchemy in Python (i)

Take SQLit3 as an example:

ImportSqlite3conn= Sqlite3.connect ('Db.sqlite3')#Get Cursor ObjectCur =conn.cursor ()#execute a series of SQL statements#Create a table#cur.execute ("CREATE TABLE demo (num int, str vachar ());")#insert Some RecordsCur.execute ("INSERT into demo values (%d, '%s ')"% (1,'AAA')) Cur.execute ("INSERT into demo values (%d, '%s ')"% (2,'BBB'))#Update a recordCur.execute ("Update demo set str= '%s ' where num =%d"% ('DDD', 3))#EnquiryCur.execute ("SELECT * from demo;") Rows=Cur.fetchall ()Print("Number of records:", Len (rows)) forIinchrows:Print(i)#Commit a transactionConn.commit ()#close a Cursor objectcur.close ()#To close a database connectionConn.close ()

Operation Result:

SQLAlchemy

SQLAlchemy is an open source software that provides a SQL Toolkit and Object Relational Mapping (ORM) tool that uses the Python language for efficient and high-performance database access design, implements a complete enterprise-class persistence model, and SQLAlchemy is very concerned about the magnitude and performance of the database.

The use of SQLAlchemy at least three parts of the code, which is the definition of the table, the definition of database connection, to increase, delete, change, check and other operations.

Examples of creating tables:

 fromSqlalchemy.ext.declarativeImportDeclarative_base fromSQLAlchemyImportColumn, Integer, Stringbase= Declarative_base ()#define an instance, all tables must inherit the instanceclassAccount (Base):__tablename__=' Account'   #Table name        #Field nameid = Column (Integer, primary_key=True) user_name= Column (String (), nullable=False) Password= Column (String (), nullable=False) Title= Column (String (50)) Salary=Column (Integer)defis_active (self):#assume that all users are active users        returnTruedefget_id (self):#returns the account ID that returns the property value to improve the encapsulation of the table        returnself.iddefis_authenticated (self):#assuming that the validation has passed        returnTruedefis_anonymous (self):#An account with a login name and password is not an anonymous user        returnFalse

Example code that defines a database connection:

 fromSQLAlchemyImportCreate_engine fromSqlalchemy.ormImportscoped_session, Sessionmaker#to define the database string used for the connection databaseDb_connect_string ='Mysql://root:[email Protected]:3306/sqlalchemy_db?charset=utf8'#If the database has an SSL connection enabled, define the SSL stringSsl_args = {    'SSL':{        'cert':'/home/ssl/client-cert.pem',        'Key':'/home/shouse/ssl/client-key.pem',        'CA':'/home/shouse/ssl/ca-cert.pem'        }    }#incoming SSL is required if the database has an SSL connection enabledEngine = Create_engine (db_connect_string, connect_args=Ssl_args)#Defining session TypesSessiontype = Scoped_session (Sessionmaker (Bind=engine, expire_on_commit=False))defgetsession ():#Create an instance of Sessiontype for database operations    returnSessiontype () fromContextlibImportContextManager#define context functions to enable automatic transaction processing,#the way to define the upper and lower file functions is to add the ContextManager adorner#execution Logic: A database session is established at the beginning of the function, and a database transaction is automatically established, and when an exception occurs, the rollback (rollback) transaction is#Close when exiting (close) connection@contextmanagerdefSession_scope (): Session=getsession ()Try:        yieldsession session.commit ()except: Session.rollback ()Raise    finally: Session.close ()

Sample code for database operations

ImportORM fromSQLAlchemyImportOr_definsertaccount (user, password, title, salary): With Session_scope () as session:#New ActionAccount = orm. Account (User_name=user, Password=password, Title=title, salary=salary) Session.add (account)defCetaccount (Id=none, User_name=none):#query operation, the query result is a collection of objects, you can also use all () to get all the dataWith Session_scope () as session:returnSession.query (ORM. account). Filter (Or_ (ORM. Account.id==id, Orm. account.user_name==user_name)). First ()defDeleteAccount (USER_NAME):#Delete OperationWith Session_scope () as Session:account= Getaccount (user_name=user_name)ifaccount:session.delete (account)defUpdateaccount (ID, user_name, password, title, salary):#Update ActionWith Session_scope () as Session:account= Session.query (orm. account). Filter (ORM. Account.id=ID). First ()if  notAccountreturnAccount.user_name=user_name Account.password=Password Account.title=title Account.salary=Salary#invoke new ActionInsertaccount ('David Li',"123","System Manager", 3000) Insertaccount ('Rebeca Li',"','Accountant', 3000)#Query OperationsGetaccount (2)#Delete OperationDeleteAccount ('David Li')#Update ActionUpdateaccount (1,"David Li","None","System Manager", 2000)

Code Explanation:

• Import the database table account with the package ORM (orm.py), introduce the multi-conditional query or the connection Or_
• Each function enables the context function Session_scope () through the WITH statement, obtains the session object through it, and automatically opens the transaction
• In Insertaccount, by creating a new table account instance and adding it to the database through Session.add, the transaction is automatically committed when the context function exits, and the new hyperplasia is called without a display session.commit ()

How to connect the mainstream database

Database	Connection string
Microsoft SQL Server	' Mssql+pymssql://username:p[email protected]:p ort/dbname '
Mysql	' Mysql://username:[email protected]:p ort/dbname '
Oracle	' Orcle://username:[email protected]:p ort/dbname '
PostgreSQL	' Postgresql://username:[email protected]:p ort/dbname '
Sqlite	' Sqlite://file_pathname '

Query condition settings:

In the actual programming process, we need to query the database records according to different conditions, SQLAlchemy query condition is called filter.

1. Equivalent filter

Session.query. Filter (account.user_name=='Jack') session.query (account). Filter (account.salary==2000)

2. Not equal to filter (! =, <,;, <=, >=)

' Jack '  ! => 3000)

3. Fuzzy query (like)

Fuzzy queries apply only to query string types, not to numeric types

# Query the user session.query (account) that contains the letter I in all names . Filter (Account.user_name.like ('%i%' ) )# Query the user session.query (account) that ends with manager in all title . Filter (Account.title.like ('  %manager')# queries a user session.query (account) with a name that begins with Da . Filter (Account.user_name.like ('da%'))

4. Including filter (In_)

# the query ID is not a 1,3,5 record session.query (account). Filter (~account.id.in_ ([1,3,5])#  Check the record Session.query (account) for which the salary is not 2000,3000,4000 . Filter (~account.salary.in_ ([2000,3000,4000])#  query all records session.query (account) for which title is not engineer and accountant . Filter (~account.title.in_ (['  Account ','Engineer']))

5. Determine if NULL (IS NULL, was NOT NULL)

# querying for records with a null value of salary  = = None)# query salary not a null value for the record  ! = none)

6. Non-logical ~

# the query ID is not a 1,3,5 record session.query (account). Filter (~account.id.in_ ([1,3,5]))

7. With logic (AND_)

#Direct multiple criteria QuerySession.query (account). Filter (account.title='Engineer', account.salary==3000)#using keyword and_ with logical query fromSQLAlchemyImportand_session.query (account). Filter (And_ (Account.title=='Engineer', account.salary==3000))#Querying with multiple filter linksSession.query (account). Filter (account.title=='Engineer'). Filter (account.salary==3000)

8. or logic (OR_)

 from Import Or_ # The query title is a record Session.query (account) for engineer or salary 3000 . Filter (Or_ (account.title=='  Engineer', account.salary==3000))

Applying SQL and SQLAlchemy in Python (i)