SQLAlchemy Tutorial-fourth Chapter-sql common query ORM Writing advanced 2-to be perfected

Content Preview

• Usage of aliases
• Use of functions
• The notation for grouping.
• The use of case.
• The use of Exists

Why do you use aliases?

• If the two columns of the two tables have the same name, you must use an alias in order to differentiate between the two columns.
• If there is a column that is calculated, make sure to give the column a name, otherwise it cannot be referenced in the query results.
  Look at the spelling of the alias.

Put the code up first, and then note that I have written in the code, please read it by yourselves ~ (#^.^#)

From models import *# alias def query_with_column_alias (): Emps = Sess.query (Emp.ename.label (' name ')). All () for item in Emps:print (item) "Output: (' SMITH ',) (' ALLEN ', ') (' WARD ',) (' JONES ',) (' MARTIN ',) ('    BLAKE ',) (' CLARK ',) (' SCOTT ',) (' KING ',) (' TURNER ',) (' ADAMS ',) (' JAMES ',) (' FORD ',) (' MILLER ') (' KATE ',) Note: The result of the output is Ganso.    Note the notation, with a comma inside the parentheses, indicating that the Ganso tuple, if not, is not considered a tuple by Python.    For example: in [1]: a = (1) in [2]: Type (a) out[2]: Int. [3]: B = (1,) in [4]: type (b) out[4]: tuple ' # alias query, and reference in query results. Def query_with_column_alias_and_call_it (): Emps = Sess.query (Emp.ename.label (' name ')). Limit (3) . All () # Please note that limit (3) means check three.    This is a special use of MySQL.    EMP1 = emps[0] Print (Type (EMP1)) # Let's look at what type of EMP1 is.    # How do I reference the Name property?    name1 = emp1.name # refers to print (' name1 ', name1) name2 = emp1[0] In the same way as an array. Print (' name2 ', name2) ' Output: <class ' sqlalchemy.uTil._collections.result ' > name1 Smith name2 Smith Note: Query results A single object is a class that implements a tuple, which can be used both as an array and as an object. But this is a frozen array and an object. Cannot be modified. That is, it cannot be used as an ordinary array or object. You cannot add attributes and elements. The use of the "#" function Def query_with_function (): From SQLAlchemy import func from Sqlalchemy.s QL Import Label # this time, I want to check the employee's salary. But does not show 10 people and everyone. For example, 9124, I only show as 9100. How do you do it? Use Python's method. It's stupid.    We call the built-in method of MySQL directly. Comms = Sess.query (Emp.ename, Emp.comm, label (' Comm_trunc ', Func.truncate (Emp.comm,-2)). Limit (3). All () for item in Co    Mms:print (Item.ename, Item.comm, Item.comm_trunc) "Output:smith 1999.00 1900 ALLEN 300.00 300    WARD 500.00: Another way to use the label here.        Before we used direct Emp.ename.label (' name ') here we used the label (' Comm_trunc ', Func.truncate (Emp.comm,-2)), which is one of the functions of the notation. Func.xxx can use all the methods in the native database and only need to add a prefix func.     can be both.        Please use it boldly if you like the native data method. For the Truncate method, refer to the https://www.w3schools.com/sql/func_mysql_truncate.asp "# Group Usage def query_and_group (): # This time, I To count everyNumber of employees in a career. From SQLAlchemy import func results = Sess.query (Emp.job, Func.count (emp.empno). Label (' Job_count ')). Group_by (Emp.job) . All () for item in Results:print (Item.job, Item.job_count) ' Output:analyst 2 Clerk 4 MA  Nager 4 President 1 salesman 4 note:group_by usage: By looking at the printed statement of the terminal, we see that SQL is such a SELECT emp.job as    Emp_job, Count (emp.job) as Job_count from the EMP group by Emp.job We understand this: GROUP by job first, then see how many people are in each group. Select subquery, the singleton query column, such as select Emp.job, this job must be the group basis.           Otherwise, it appears in the SELECT statement.    The aggregate function column inside the subquery, there is no limit, you can arbitrarily use the aggregate function to data processing any column.    Why do we use count as a secondary?        Back to our purpose, we want to query how many people are in each group. A third way of writing a label: Use the. Label directly behind the function. This is a chained notation.     I never thought I could write it this way, so I tried to write it all out. The reason is that the English document is not read carefully, do not know how to use it.    Secondly, the English document reading is too slow, the English level is limited, unable to dig into the usage of SQLAlchemy ￣-￣| | The use of the "# Group Def Query_and_group_plus (): # This time, we want to count the number of employees in each profession. And we're going to add an extra group basis, where the employee is located (the Loc field in the Department table), so we're going to make a joint query from SQLAlchemy import func results = Sess.query (Dept.loc, Emp.Job, Func.count (emp.empno). Label (' Job_count '). Join (EMP, Emp.deptno = = Dept.deptno). group_by (Dept.loc, E Mp.job). All () to item in Results:print (Item.loc, Item.job, Item.job_count) ' Output:chicago CL ERK 1 CHICAGO Manager 1 CHICAGO salesman 4 DALLAS ANALYST 2 DALLAS Clerk 2 DALLAS MANAGER 1 NEW YORK CLE RK 1 New York MANAGER 1 New York President 1 Note: Through this query, you should be able to clearly see the distribution of various positions in various regions. You can also dig up some information from the query results.    This is the role of statistics.        This time, we also tell you that we can use multiple columns for group statistics. "If __name__ = = ' __main__ ': Query_and_group_plus ()

The grammar of Case_when and exists is mended in the afternoon.

SQLAlchemy official website
Http://docs.sqlalchemy.org/en/latest/contents.html
Tutorials All code GitHub address
Https://github.com/notfresh/sqlalchemy_demo

SQLAlchemy Tutorial-fourth Chapter-sql common query ORM Writing advanced 2-to be perfected