Basic Primer _python-modules and packages. In-depth sqlalchemy to play a variety of additions and deletions to change the operation?

Add Data:

# single-row data (from SQLAlchemy import ...)

Conn = engine.connect () #  mode one:  the Insertins = cookies.insert () of the calling Table object. VALUES (     cookie_name= ' chocolate chip ',     cookie_recipe_url= '/http Some.aweso.me/cookie/recipe.html ',     cookie_sku= ' CC01 ',     quantity= '     unit_cost= ' 0.50 ') print  ' values: => {0} '. Format (Ins.compile (). params) result = conn.execute (INS) print  ' instid: => {0} '. Format (result.inserted_ Primary_key) #  mode two:  call Global object Insertins = insert (cookies). VALUES (    cookie _name= ' chocolate chip ',     cookie_recipe_url= ' http://some.aweso.me/cookie/recipe.html ',     cookie_sku= ' CC01 ',     quantity= ',     unit _cost= ' 0.50 ') print  ' values: => {0} '. Format (Ins.compile (). Params) result =  Conn.execute (INS) print  ' Instid:  => {0} '. Format (result.inserted_primary_key) #  mode three:  call execute ins =  Cookies.insert () Result = conn.execute (    ins,    cookie_ Name= ' chocolate chip ',     cookie_recipe_url= ' http://some.aweso.me/cookie/recipe.html ' ,     cookie_sku= ' CC01 ',     quantity= ',     unit_ cost= ' 0.50 ') print  ' values: => {0} '. Format (Ins.compile (). Params) result =  Conn.execute (INS) print  ' instid: => {0} '. Format (Result.inserted_primary_key)

Description: As shown on the three ways to add data, such as the first call, specify the table's inset () to initialize an insert instance, and then call its values method to populate the default INSERT statement in the column corresponding to the value, and eventually return an Insert object, by Ins.compile (). The params can return the inserted key-value pair in the form of a dictionary, eventually calling execute of the Conn connection object to execute the INS object compile the translated SQL statement, returning the execution result object, You can also return the list of IDs of inserted records through the result object Result.inserted_primary_key

# Multiline data (from SQLAlchemy import ...)

Conn = engine.connect () inventory_list = [    {          ' cookie_name ':  ' Peanut butter ',          ' Cookie_recipe_url ':  ' http://some.aweso.me/cookie/peanut.html ',          ' Cookie_sku ':  ' PB01 ',         ' quantity ':  ' 24 ',          ' unit_cost ':  ' 0.25 '     },     {         ' cookie_name ':  ' Oatmeal raisin ',          ' Cookie_recipe_url ':  ' http://some.okay.me/cookie/raisin.html ',          ' Cookie_sku ':  ' EWW01 ',          ' quantity ':  ',         ' unit_cost ':  ' 1.00 '     }] Ins = insert (Cookies) result = conn.execute (ins, inventory_list) print  ' Instid:  {0} '. Format (RESULT.LASTROWID)

Description: The Resultproxy object returned by Conn.execute can be result.lastrowid to get the ID value of the last row inserted when multiple rows are inserted, as shown on the most commonly used multiline insert data.

Query data:

# All fields (from SQLAlchemy import ...)

conn = Engine.connect () # Method One: Call Global Object selects = select ([Cookies.c.id, Cookies.c.cookie_name, Cookies.c.cookie_recipe_url , Cookies.c.cookie_sku, Cookies.c.quantity, cookies.c.unit_cost]) R = Conn.execute (s) k = R.keys () result = [Dict (Zip (k, V )) for V in R.fetchall ()]print ' values: {0} '. Format (Result) # Method two: Call table-level Object selects = Cookies.select () R = Conn.execute (s) k = R . keys () result = [Dict (Zip (k, v)) for V in R.fetchall ()]print ' values: {0} '. Format (Result)

Description: As shown on the two ways to query data, first instantiation, such as the first call, a select instance, the instance parameter must be a list, if the list is a table name to query all columns, otherwise query the specified column, Cookies.c.id is actually the column (ID) object, Then call Conn.execute execute this Select object compile the translated SQL statement, then return the Resultproxy object, then call Fetchall of the Resultproxy object to get all the rows, The Resultproxy object has a special method keys () to get all the column names, sometimes the template rendering is more convenient when the dictionary form.

# Result Object (from SQLAlchemy import ...)

Conn = engine.connect () S = select ([    cookies.c.id,  cookies.c.cookie_name, cookies.c.cookie_recipe_url,    cookies.c.cookie_sku,  Cookies.c.quantity, cookies.c.unit_cost]) #  Description:  r is the cursor proxy for resultproxy, and the cursor is a support iteration, More recommended iterative get result set R = conn.execute (s) for record in r:    print  ' type => {} values => {} '. Format (type (record),  record) #  description:  R.fetchall () returns a list, but each element in the list is automatically converted to Rowproxy object R = conn.execute (s) after it is fragmented # get a wrapper  of cursor, ResultProxyprint  ' type => {} '. Format (type (r)) # get all  row of the resultproxy, listallrows = r.fetchall () print  ' type = > {} values => {} '. Format (type (allrows),  allrows) # get one row  of the list, rowproxyonerow = allrows[0]print  ' type => {} values => {} '. Format (type (Onerow),  onerow) #  Description:  COOKIES.C is a collection of column objects in the cookie, which can be traversed through. or [] to get the value of the corresponding column # get all col  of the row, valuefor key in cookies.c:    oneval =  getattr (onerow, key.name)     print  ' type => {} values  => {} '. Format (type (oneval),  oneval) #  Description:  r.fetchone Each fetch is the first one from the location of the last fetch, And returned is also the Rowproxy object R = conn.execute (s) onerow = r.fetchone () print  ' type => { } values => {} '. Format (type (onerow),  onerow) #  Description:  r.first () always returns the first record in the result set, and returns the Rowproxy object R = conn.execute (s) firstrow = r.first () print  ' type => {}  values => {} '. Format (type (firstrow),  firstrow) #  Description:  r.scalar () Always returns the value of the first column of the first record in the result set, and only makes sense when one row is returned r = Conn.execute (s) ids = r.scalar () print  ' type => {} values => {} '. Format (type (IDS),  ids)

Description: As shown on the various proxy result set operation methods, it is interesting to note that the Resultproxy object Fetchall returned is a tuple list and the fragmented elements are automatically converted to Rowproxy objects, can also be obtained through Fetchone/first/scalar, Gets the Rowproxy object that gets the value of the corresponding column, either through the. or [table instance. C. Column name] method.

# Sort related (from SQLAlchemy import ...)

conn = Engine.connect () s = select ([Cookies.c.quantity, Cookies.c.cookie_name]). ORDER_BY (Desc (cookies.c.quantity)) R = Conn.execute (s) for record in R:print ' {0}-{1} '. Format (record.quantity, Record.cookie_name)

Description: As shown above, general sorting by the specified column operation method, by the Resultproxy call order_by sort, the parameter is the specified Column object, the default forward sort, can be through DESC (cookies.c.quantity) or Cookies.c.quantity.desc () in reverse order, but it is more recommended to use DESC (cookies.c.quantity) in this way.

# Limit number of rows (from SQLAlchemy import ...)

conn = Engine.connect () s = select ([Cookies.c.quantity, Cookies.c.cookie_name]). order_by (cookies.c.quantity). Limit (2 ) R = Conn.execute (s) for record in R:print ' {0}-{1} '. Format (record.quantity, Record.cookie_name)

Description: As shown above, the limit number of rows is sorted by the specified column, the number of rows is restricted by the resultproxy call limit, the parameter is the specified row for the restriction output

# aggregation function (from sqlalchemy.sql import functions)

conn = Engine.connect () # Description: Sum s = select ([Functions.sum (cookies.c.quantity)]) R = Conn.execute (s) print ' values: {0} '. Format (R.scalar ()) # Description: Count s = select ([Functions.count (cookies.c.id). Label (' Inventory_count ')]) R = Conn.execute (s) result = R.first () print ' values: {0} '. Format (Result.inventory_count)

Description: As shown on the common aggregate function sum and count,sum, default does not use label to define a column alias by default with Sum_1/count_1...sum_n/count_n as the Rowproxy property, otherwise you can directly call Result.label _name Gets the new column value, of course, the simpler and more brutal way is to call the scalar, because it always outputs the value of the leftmost column

# various operations (from SQLAlchemy import ...)

conn = Engine.connect () # Description: Supports almost all operators in py s = select ([Cookies.c.id, (' xmdevops-' + cookies.c.cookie_sku). Label (' Cookie_ Sku_alias ')] r = Conn.execute (s) for record in R:print type (record), Record.items () # Description: Supports operation parameters Flexible conversion s = SELECT ([Cookies . C.id, Cookies.c.unit_cost.cast (Numeric (2))]) R = Conn.execute (s) for record in R:print type (record), Record.items ()

Description: As shown above, the common operator and cast of a special conversion type can be called as a property of the Column object, or cast can be called directly, and its last argument can be any data type supported by SQLAlchemy

# Filter data (from SQLAlchemy import ...)

Conn = engine.connect () # expressions = select ([cookies]). WHERE (     cookies.c.quantity == 12). where (    cookies.c.cookie_name == u ' Chocolate chip ') R = conn.execute (s) for record in r:    print  type (record),  dict (Record.items ()) # like / notlikes = select ([cookies]). Where (    cookies.c.cookie_name.like ('%chocolate% ')) R = conn.execute (s) for  Record in r:    print type (record),  dict (Record.items ()) #  Betweens = select ([cookies]). WHERE (    cookies.c.id.between (16, 32)) r  = conn.execute (s) for record in r:    print type (record),  Dict (Record.items ()) # concats = select ([cookies]). WHERE (     Cookies.c.id.concat (cookies.c.quantity) &NBsp;==  ' 2424 ') R = conn.execute (s) for record in r:    print  type (record),  dict (Record.items ()) # distincts = select ([Cookies.c.cookie_ Name.distinct ()]) R = conn.execute (s) for record in r:    print  type (record),  dict (Record.items ()) # in_ / notin_s = select ([cookies]). where (    cookies.c.cookie_name.in_ ([u ' chocolate chip ',  u ' Oatmeal raisin ']) ) R = conn.execute (s) for record in r:    print type (record),  dict (Record.items ()) # is_ / isnots = select ([cookies]). WHERE (     cookies.c.cookie_name.is_ (None)) R = conn.execute (s) for record in r:     print type (record),  dict (Record.items ()) # containss = select ([cookies]). Where (    cookIes.c.cookie_name.contains (' raisin ')) R = conn.execute (s) for record in r:     print type (record),  dict (Record.items ()) # endswiths = select ([cookies]). Where (    cookies.c.cookie_name.endswith (' raisin ')) R = conn.execute (s) for  Record in r:    print type (record),  dict (Record.items ()) #  Startswiths = select ([cookies]). WHERE (    cookies.c.cookie_name.startswith (' Chocolate ')) R = conn.execute (s) for record in r:    print  Type (record),  dict (Record.items ()) # and_ / or_ / not_s = select ([ Cookies]). WHERE (    and_ (cookies.c.quantity > 23, cookies.c.unit_cost  < 0.40)) R = conn.execute (s) for record in r:     Print type (record),  dict (recoRd.items ()) 

Description: The commonly used built-in functions are shown above LIKE/NOTLIKE/BETWEEN/CONCAT/DISTINCT/IN_/NOTIN_/IS_/ISNOT/CONTAINS/STARTSWITH/ENDSWITH/AND_/OR_ /not_ operation method, familiar with MySQL should be not unfamiliar with it ~ here is not to explain the ~

Update data:

conn = Engine.connect () u = Update (cookies). WHERE (Cookies.c.cookie_name = = ' peanut butter '). VALUES (QUANTITY=COOKIES.C. Quantity * 2) R = Conn.execute (u) print ' values: {0} '. Format (r.rowcount) s = select ([Cookies.c.cookie_name, Cookies.c.quantity]). WHERE (Cookies.c.cookie_name = = ' Peanut butter ') R = Conn.execute (s) for record in R:print type ( Record), record

Description: The usual method of updating data is shown above, similar to update and insert usages, but supports the where condition to limit the update of the specified column, otherwise all columns will be updated.

Delete data:

conn = Engine.connect () d = delete (cookies). WHERE (Cookies.c.cookie_name = = ' Peanut butter ') R = Conn.execute (d) print ' Values: {0} '. Format (R.rowcount)

Description: As shown on the usual method of deleting data, the difference between delete and Update/insert is that you do not need values, but support where to restrict the deletion of the specified column, otherwise all columns will be deleted.

Multiple table connections:

conn = Engine.connect () # INNER JOIN: Join, only two tables match the row to appear in the result set S = select ([Orders.c.id, Users.c.username, line_items.c.quantity , Cookies.c.cookie_name]). Select_from (Orders.join (users). Join (Line_items). Join (Cookies). WHERE (Users.c.username  = = ' Cookiemon ') R = Conn.execute (s) for record in R:print type (record), Dict (Record.items ()) # Left connection: Outerjoin, left table without limit s = Select ([Line_items.c.id, Line_items.c.quantity, Cookies.c.cookie_name]). Select_from (Line_items.outerjoin (cookies , line_items.c.cookie_id = = cookies.c.id)) R = Conn.execute (s) for record in R:print type (record), Dict (Record.items ())

Description: As shown on the use of common self-join join and left-outerjoin usage, the new version does not support the right to connect, full connection, but basically using self-connect and left connection has almost enough to meet all our needs.

Table level Name:

conn = Engine.connect () c = Cookies.alias () s = select ([C.c.id, Cookies.c.cookie_name]). WHERE (c.c.id = = cookies.c.id) R = Conn.execute (s) for record in R:print type (record), record

Description: As shown on the commonly used table alias usage, especially in multi-table association, will be used in many different table names, and some table names may stink long smelly, not very good memory, you can first define an alias and then manipulate the corresponding table ~

Group query:

conn = Engine.connect () s = select ([Users.c.username, Functions.sum (orders.c.id)]). Select_from (Orders, Users.c.id = = orders.c.user_id)). Group_by (users.c.username) R = Conn.execute (s) for record in R:print type (record), reco Rd

Description: As shown on the commonly used group query usage, query the order volume of each user, first through the joins join joins the multi-table query, and then by the group_by by the user name to group, the idea and the SQL very similar ~

Rawsql:

conn = Engine.connect () # full Raw Sqls = ' SELECT * from users ' r = Conn.execute (s) for record in R:print type (record), Recor d# semi-Raw SQLS = select ([users]). where (Text ("Username= ' Limanman ')") R = Conn.execute (s) for record in R:print type (record) , record

Description: As shown on the use of common bare sql, in fact, the work is not very common, but sometimes sqlalchemy does not implement the corresponding interface when the bare SQL is also a solution, if you want to semi-naked state need to use text to parse the first.

This article is from the "Li-Yun Development Road" blog, please be sure to keep this source http://xmdevops.blog.51cto.com/11144840/1869943

Basic Primer _python-modules and packages. In-depth sqlalchemy to play a variety of additions and deletions to change the operation?