python pymysql和orm

pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。

1. pymysql

安装：

管理员打开cmd，切换到python的安装路径，进入到Scripts目录下（如：C:\Users\Administrator\AppData\Local\Programs\Python\Python35\Scripts）；
执行以下命令：pip install pymysql
校验是否安装成功：进入到python命令行模式，输入import pymysql，无报错代表成功。

1.2 使用操作

1.2.1 连接数据库

import pymysql

db = pymysql.connect("数据库ip","用户","密码","数据库" ) # 打开数据库连接
cursor.execute("SELECT VERSION()")                    # 使用 execute() 方法执行 SQL 查询
data = cursor.fetchone()                              # 使用 fetchone() 方法获取单条数据
print ("Database version : %s " % data)
db.close()                                            # 关闭数据库连接

1.2.2 创建表

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
 
# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
 
cursor.execute(sql)
 
# 关闭数据库连接
db.close()

1.2.3 查询数据

fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
fetchall(): 接收全部的返回结果行.
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

import pymysql
#创建链接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123123', db='t1')
#创建游标
cursor = conn.cursor()
#执行sql
cursor.execute("select * from students")
   
# 获取第一行数据
row_1 = cursor.fetchone()
   
# 获取前n行数据
row_2 = cursor.fetchmany(3)
# 获取所有数据
row_3 = cursor.fetchall()
# 提交，不然无法保存新建或者修改的数据
conn.commit()
#关闭游标
cursor.close()
#关闭链接
conn.close()

1.2.2 执行sql

import pymysql
   
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123123', db='t1')
# 创建游标
cursor = conn.cursor()
   
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
   
# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
   
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
   
   
# 提交，不然无法保存新建或者修改的数据
conn.commit()
   
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

2. ORM sqlachemy

2.1 对象映射关系（ORM）

orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

优点：

隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来
ORM使我们构造固化数据结构变得简单易行

缺点：

无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的

2.2 SQLAlchemy

在Python中，最有名的ORM框架是SQLAlchemy。用户包括openstack＼Dropbox等知名公司或应用
Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
     
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
     
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
     
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
     
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

　　注：支持连接MySQL、Oracles数据库

安装：

pip install SQLAlchemy
pip install pymysql
#由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互

2.3 创建

下面就开始让你见证orm的nb之处，盘古开天劈地之前，我们创建一个表是这样的：

CREATE TABLE user (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    password VARCHAR(64),
    PRIMARY KEY (id)
)

这只是最简单的sql表，如果再加上外键关联什么的，一般程序员的脑容量是记不住那些sql语句的，于是有了orm,实现上面同样的功能，代码如下：

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
  
engine = create_engine("mysql+pymysql://root:alex3714@localhost/testdb",
                                    encoding='utf-8', echo=True)
  
  
Base = declarative_base() #生成orm基类
  
class User(Base):
    __tablename__ = 'user' #表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))
  
Base.metadata.create_all(engine) #创建表结构

最基本的表我们创建好了，那我们开始用orm创建一条数据试试

Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class() #生成session实例
  
  
user_obj = User(name="cc",password="123123") #生成你要创建的数据对象
print(user_obj.name,user_obj.id)  #此时还没创建对象呢，不信你打印一下id发现还是None
  
Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建
print(user_obj.name,user_obj.id) #此时也依然还没创建
  
Session.commit() #现此才统一提交，创建数据

2.4 查询

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() ) #分组

不过刚才上面的显示的内存对象对址你是没办法分清返回的是什么数据的，除非打印具体字段看一下，如果想让它变的可读，只需在定义表的类下面加上这样的代码

def __repr__(self):
    return "<User(name='%s',  password='%s')>" % (
        self.name, self.password)

2.5 修改

my_user = Session.query(User).filter_by(name="cc").first()
  
my_user.name = "TT"
  
Session.commit()

2.6 回滚

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() ) #再查就发现刚才添加的数据没有了。
  
# Session
# Session.commit()

2.7 外间关联

我们先创建个study_record表与student进行关联

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import String,Column,Integer,ForeignKey,DATE
from sqlalchemy.orm import sessionmaker,relationship
  
engine = create_engine("mysql+pymysql://root:zyw@123@192.168.1.18/testdb",
                       encoding="utf-8")
  
Base = declarative_base()
  
class Student(Base):
    __tablename__ ="student"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),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_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32),nullable=False)
    stu_id = Column(Integer,ForeignKey("student.id")) #关联student表里的id
  
    my_student = relationship("Student",backref="my_study_record") # Student为关联的类

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()

posted @ 2016-11-04 00:19 Crazy丶迷恋阅读(2510) 评论(0) 编辑收藏举报

刷新页面返回顶部

Crazy丶迷恋