Python: Database Operations Module SQLAlchemy

The SQLAlchemy ORM is a mapping function (mapper) that will be defined in Python classWith the data in the database TableEstablish associations, and the class's example (instance)and table of rows (Row)Establish an association . View the database tables for a class, using the __tablename__ property, such as user.__tablename__   1. Querying data (query)   1.1 Query the number of flow in a trace (to count flows of specific trace)Session.query (Flow). Filter (flow.trace_id = = 1). count ()  

1.2. Query the number of different srcip in a trace (to count distinct SRCIP)

From sqlalchemy Import distinctfrom config import *session = dbsession () session.query (FLOW.SRCIP). Filter (flow.trace_id = = 1). distinct (). count () 1.3 Querying the number of different dstip and dstport pairs in a trace (to count distinct dstip and dstport)Session.query (Flow.dstip, Flow.dstport). Filter (flow.trace_id = = 1). Distinct (). COUNT () 1.4 Queries the data for the specified column, returning a list of keyedtuple data types (get a tuple list of specified columns)n = session.query (Flow.dstip, Flow.dstport). Filter (flow.trace_id = = 1) . All ()# The type of n is list.#, the type of n[0] is sqlalchemy.util._collections.Keyedtuple   1.5 Querying all the different values in the specified column (get a distinct tuple list of specified columns)n = session.query (Flow.dstip, Flow.dstport). Filter (flow.trace_id = = 1) . Distinct (). All () 1.6 Get the average of a column of data (get average value of a column)# SQL Language:select avg (TXPKT) from Flowfrom sqlalchemy.sql import Funcq = Session.query ( Func.avg(FLOW.TXPKT)). Filter (flow.trace_id = = 1) print q[0][0]# The type of Q is sqlalchemy.orm.query.query# the type of q[0] is sqlalchemy.util. _collections. keyedtuple# the type of q[0][0] is decimal. Decimal 1.7 Calculation of data averages for multiple columns (compute average values of columns)Q = Session.query ((Func.avg (FLOW.TXPKT) +Func.avg (FLOW.RXPKT)) /2). Filter (flow.trace_id = = 1) 1.8 Sorting the data to be queried (order by)From sqlalchemy Import descq = Session.query (Flow.timestamp). Filter (trace_id = = 1). order_by (Desc (Flow.timestamp)) 1.9 Group QueriesQ = Session.query (Flow.dstip, Flow.dstport, Func.count (flow.id)). Filter (flow.trace_id = = tid). group_by(Flow.dstip, Flow.dstport). All () 2 queries, the common filtering operation equals (equals), for example Query.filter (name ==' Jack ') is not equal to (not equals), such as Query.filter (name !=' Jack ') in the list (in), for example Query.filter (name. In_([' Micheal ', ' Bob ', ' Jack ']) is not in the list (not in), such as Query.filter ( ~Name. In_([' Micheal ', ' Bob ', ' Jack ']) Null value (NULL), such as Query.filter (name = = None) is not a null value (not NULL), such as Query.filter (name ! = None) with (and), such as Query.filter ( And_(name = = ' Andy ', fullname = = ' Andy Liu ') And_ can be omitted, such as Query.filter (name== ' Andy ', fullname== ' Andy Liu ') or (or), such as Query.filter ( Or_(name = = ' Andy ', name = = ' micheal ')) 2. Data Operations for tables (table data operation) 2.1 add \ Delete a column (add a new column to a table)From DB import enginefrom sqlalchemy Import ddladd_column = DDL(' ALTER TABLE Flow 'AddColumn cluster_id integer after trace_id ') Drop_column = DDL(' ALTER TABLE Flow 'DropColumn microsecond ') engine. Execute(Add_column) engine. Execute(Drop_column) 2.2 Modifying a data (update a value)Session.query (Flow). Filter (Flow.dstip = = Dstip, Flow.dstport = = dstport, flow.trace_id = = 1). Update({' cluster_id ': 0}) 2.3 Inserting a row of data (insert a row)Session = Dbsession () cluster = Clusters (trace_id = tid, cluster_id = CID, \ dstip = DIP, dstport = Dport, \ avgpkt = apkt, avgbyte = abyte, \ size = count) session. Add(cluster) Session.commit () # Commit or flush Session.close () 2.4 Delete a row of data (delete a row)Session = Dbsession () session.query (Clusters). Filter (clusters.trace_id = 2). Delete () Session.commit () # Commit or FL Ush Session.close () Supplement: foreign key ForeignKey can only refer to values that already exist in the specified column of the appearance.

Python: Database Operations Module SQLAlchemy