The SQLAlchemy of Python tools

Source: Internet
Author: User
Tags sql using

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.