Tool Description: SQLAlchemy is an ORM framework in the Python programming language, built on top of the database API, using relational object mappings for database operations, in short: converting objects to SQL and then executing SQL using the data API and getting execution results.
How to work: manipulate a database by defining a class, a table in a database, a row of data in a class object corresponding to a table, inserting data into a database table by instantiating a class, and getting the value of the corresponding field in the table by using the object. Field name.
Take a specific example to illustrate:
#!/usr/bin/env python# -*- coding:utf-8 -*-from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import column, integer, string, Datetimefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy import create_ engine# configuration of the MySQL database connection information in the form of a dictionary mysql_dic = { ' mysql_user ': ' Test ', ' mysql_pass ': ' 1234 ', ' mysql_ip ': ' 192.168.192.168 ', ' mysql_port ': 3306, ' mysql_db ': ' Test ',}class connect (object): # create a class that specifically connects to the database base = declarative_base () # base class for creating objects def __init__ (self): mysql_str = "mysql+mysqldb://{mysql_user}:{mysql_pass}@{mysql_ip}:{mysql_port}/{mysql_db}" # command line to connect to a database mysql_con = mysql_str.format (**mysql_dic) # formatting Commands self.engine = create_engine (mysql_con, max_overflow=5) # Initializing database connection session = sessionmaker (bind=self.engine) # Create a session to manipulate the database self.session = session () def Create_table (self): "Look for all subclasses of base, generate corresponding data table information in the database according to the structure of the subclass" connect.base.metadata.create_all (Self.engine) class server (Connect.Base): # define a class (actually a database table) inherit base base class __tablename__ = ' server ' # table names, all of the following fields are table fields id = column (integer,autoincrement=1,primary_key=true) date = Column (DATETIME) user = column (String) ip = column ( String (()) group = column (string) if __name__ == ' __main__ ': c = connect () # connect the database and create a session between the program and the database C.create_table () # create a defined server table # Add Data s = server (user= ' test ', ip= ' 192.168.100.1 ', group= ' monitor ') # Instantiate a Class object c.session.add (s) # add a row of data (a class object) to the table through session sessions # c.session.add_all ([ # server (user= ' test ', ip= ' 192.168.100.2 ', group= ' monitor '), # server (user= ' test ', ip=' 192.168.100.3 ', group= ' monitor '), # ]) # add more than one data at a time c.session.commit () # submit # Delete data (delete data in the server table with IDs greater than 2) c.session.query (Server). Filter (server.id > 2). Delete () C.session.commit () # Modify the data (the Group field value for modifying data with IDs greater than 2 in the server table is Java) c.session.query (server). Filter (server.id > 2). Update ({' Group ' : ' JAVA '}) c.session.commit () # Querying data (querying all data in the server table for which the user field value is test) ret = C.session.query (Server). filter_by (user= ' test '). All () for i in ret: print i.ip # gets the value of the IP field in the data that meets the criteria c.session.close () # Close session
part of the blog content and ideas organized from Wu Jianzi blog .
This article from "A rookie on the Sky" blog, please be sure to keep this source http://rmeos.blog.51cto.com/761575/1737775
The SQLAlchemy of Python tools