Python Pymysql and ORM

Pymsql is a module that operates MySQL in Python and is used almost the same way as MySQLdb.

1. Installation

• The administrator opens cmd, switches to the Python installation path and enters into the Scripts directory (for example: C:\Users\Administrator\AppData\Local\Programs\Python\Python35\Scripts) ；

• Execute the following command: Pip install Pymysql

• Check whether the installation is successful: Enter the python command line mode, enter import pymysql, no error indicates success;

2. Use the action 2.1 Querying Data

Import pymysql# Create Link conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123123 ', db= ' T1 ') # Create a cursor cursor = conn.cursor () #执行sqlcursor. Execute ("SELECT * from students")   # Gets the first row of data Row_1 = Cursor.fetchone ()   # Get first n rows of data Row_2 = Cursor.fetchmany (3) # get all data Row_3 = Cursor.fetchall () # Commit, or you cannot save new or modified Data conn.commit () # Close the cursor cursor.close () #关闭链接conn. Close ()

2.2 Execute SQL

Import Pymysql   # Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123123 ', db= ' T1 ') # Create a cursor cursor = conn.cursor ()   # Execute SQL and return the number of affected rows Effect_row = Cursor.execute ("update hosts set host = ' 1.1.1.2 '")   # Execute S QL, and returns the number of rows affected #effect_row = Cursor.execute ("update hosts set host = ' 1.1.1.2 ' where nid >%s", (1,))   # Executes SQL and returns the number of affected rows # Effect_row = Cursor.executemany ("INSERT into hosts (host,color_id) VALUES (%s,%s)", [("1.1.1.11", 1), ("1.1.1.11", 2)])      # Commit, otherwise unable to save new or modified Data conn.commit ()   # Close Cursor cursor.close () # Close connection Conn.close ()

Three. ORM Sqlachemy

1. Object mapping Relationship (ORM)

ORM English Full Name Object relational mapping, that is, objects mapping relation program, simply say we like python this object-oriented program is all objects, but we use the database is relational, in order to ensure consistent use of the habit, By ORM, the object model of the programming language and the relational model of the database are mapped, so that we can use the programming language to manipulate the database directly using the object model of the programming language, rather than using the SQL language directly.

Advantages:

• Hidden data access details, "closed" Universal database interaction, ORM Core. He makes our universal database interaction simple and easy, without having to think about the damned SQL statements at all. Rapid development, resulting from
• ORM makes it easy to construct solidified data structures

Disadvantages:

• Inevitably, automation means mapping and association management at the expense of performance (early, this is what all dislike orm people have in common). Now the various ORM frameworks are trying to use various methods to alleviate this (lazyload,cache), the effect is very significant

2. SQLAlchemy

In Python, the most famous ORM framework is sqlalchemy. Users include well-known companies or applications such as Openstack\dropbox

Dialect is used to communicate with the data API, invoking different database APIs depending on the configuration file, enabling operations on the database, such as:

Mysql-python    mysql+mysqldb://<user>:<password>@
Note: Support for connecting MySQL, oracles database
2.1 Installation:
Pip Install Sqlalchemypip install pymysql# because MYSQLDB still does not support py3, so here we use Pymysql and sqlalchemy to interact with
2.2 Basic use:
Here's where you'll witness the ORM's NB, before Pangu opens the heavens, we create a table like this:
CREATE TABLE User (    ID INTEGER not NULL auto_increment,    name varchar (+),    password varchar),    PRIMARY KEY (ID))
This is just the simplest SQL table, if you add a foreign key association or something, the general Programmer's brain capacity is not remember those SQL statements, so there is an ORM, to achieve the same function above, the code is as follows:
Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy import Column, Integer, String  engine = Create_engine ("Mysql+pymysql://root:[email protected]/testdb",                                    encoding= ' Utf-8 ', echo=true)    Base = Declarative_base () #生成orm基类  class User (base):    __tablename__ = ' user ' #表名    id = Column (Integer, primary _key=true)    name = Column (string ())    Password = column (string)  Base.metadata.create_all (engine) # CREATE TABLE structure
The most basic table we've created, so let's start with an ORM and try to create a piece of data.
Session_class = Sessionmaker (bind=engine) #创建与数据库的会话session class, notice that this is a class that is returned to the session, not the instance session = Session_ Class () #生成session实例    user_obj = User (name= "cc", password= "123123") #生成你要创建的数据对象print (user_obj.name,user_obj.id)  #此时还没创建对象呢, I don't believe you print out ID or none  . Session.add (User_obj) #把要创建的数据对象添加到这个session里, a unified print (User_obj.name,user_obj.id) is created #此时也依然还没创建  Session.commit () #现此才统一提交, creating data
2.2.1 Query
My_user = session.query (user). filter_by (name= "CC"). First () print (My_user) #打印输出: <__main__. User object at 0x105b4ba90>print (My_user.id,my_user.name,my_user.password) #打印输出: CC 123123

print(Session.query(User.name,User.id).all() )　　#获取所有数据
objs = Session.query(User).filter(User.id>0).filter(User.id<7).all() #多条件查询

Session.query(User).filter(User.name.like("Ra%")).count() #统计

from sqlalchemy import func
  print(Session.query(func.count(User.name),User.name).group_by(User.name).all() ) #分组
However, just above the memory object to the address you are not able to distinguish what data is returned, unless you print the specific fields to see if you want to make it readable, just add such code under the class of the definition table
def __repr__ (self):    return "<user (name= '%s ',  password= '%s ') >"% (        self.name, Self.password)
2.2.2 Modification
My_user = session.query (user). filter_by (name= "CC"). First ()  my_user.name = "TT"  session.commit ()
2.2.3 Rollback
My_user = session.query (user). filter_by (id=1). First () My_user.name = "Jack"    fake_user = User (name= ' Rain ', password = ' 12345 ') session.add (fake_user)  print (session.query (user). Filter (User.name.in_ ([' Jack ', ' rain ']). All ())  #这时看session里有你刚添加和修改的数据  Session.rollback () #此时你rollback一下  print (Session.query (User). Filter (User.name.in_ ([' Jack ', ' rain ']). All ()) # Check again to find that the data you just added is gone.  # session# Session.commit ()
2.2.4 Foreign Key Association
Let's start by creating a Study_record table to associate with student.
From SQLAlchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy Import String,column,integer,foreignkey,datefrom sqlalchemy.orm import Sessionmaker,relationship engine = Create_engine (" Mysql+pymysql://root:[email protected]@192.168.1.18/testdb ", encoding=" Utf-8 ") Base = Declarat Ive_base () class Student (base): __tablename__ = "Student" id = column (integer,primary_key=true) name = Column (Str ING (+), nullable=false) register_date = Column (date,nullable=false) def __repr__ (self): return "<%s name" :%s> "% (Self.id,self.name) class Studyrecord (Base): __tablename__ =" Study_record "id = Column (integer,primary_ke y=true) Day = column (integer,nullable=false) status = Column (String (+), nullable=false) stu_id = column (integer,f Oreignkey ("Student.id")) #关联student表里的id my_student = relationship ("Student", backref= "My_study_record") # Student is associated Class Def __repr__ (self): return "<%s name:%s> "% (self.id, self.name) Base.metadata.create_all (engine) Session_class = Sessionmaker (bind=engine) Session = Session_class () S1 = Student (name= "CC", register_date= "2016-10-28") s2 = Student (name= "tt", register_date= " 2015-10-28 ") s3 = Student (name=" RR ", register_date=" 2014-10-28 ") S4 = Student (name=" YY ", register_date=" 2013-10-28 ") R1 = Studyrecord (day=1,status= "YES", stu_id=1) r2 = Studyrecord (day=2,status= "No", stu_id=1) R3 = Studyrecord (day=3,status = "YES", stu_id=1) R4 = Studyrecord (day=1,status= "yes", stu_id=2) Session.add_all ([S1,S2,S3,S4,R1,R2,R3,R4]) Session.commit ()
　　
 
Python Pymysql and ORM