python操作数据库基础

数据库分类概述

　　SQL

　　　　mysql\oracle\sql server\db2..

　　NoSQL

　　　　mongodb\neo4j\hbase..

　　区别：

　　　　cap

　　　　对数据丢失容忍度

开发环境介绍

　　mysql

　　navicat

开发包

　　mysql-connector

　　　　- http://dev.mysql.com/downloads/connector/python

　　MYSQLdb

　　　　- https://pypi.python.org/pypi/MySQL-python

　　　　pip install mysql-connector

　　SQLAlchemy

　　　　- http://www.sqlalchemy.org

代码模式：

　　创建连接：connect

　　创建游标：cursor

　　执行sql：excute、executemany

　　　　　　一次提交一条sql语句

　　　　　　　　　　 一次提交多条sql语句

　　关闭连接：close

　　参数化SQL：%s

# !/usr/bin/python
# encoding:utf8


#导入连接包
from mysql import connector
#建立连接
params=dict(host='localhost',port='3306',user='root',password='',database='test')
conn =connector.connect(**params)
#创建游标
curs=conn.cursor()
# DDL语句，创建表、视图、索引
# ddl="""
#     create table users(id integer,name varchar(40),address varchar(100))
# """
# try:
#     curs.execute(ddl)
# except Exception,e:
#     print '异常+&s'%e
# DML语句，增删查改
# 参数化
# 参数化sql插入语句
mysqlinsert="""
    insert into users(name,address) values(%s,%s)
"""
#参数
u1=('huang','zizhou')
#传参，使用游标名进行操作
curs.execute(mysqlinsert,u1)
#提交事物，保证持久性，不会回滚。使用连接名进行操作
conn.commit()
#批量参数
us=[
    ('huang1','zizhou12'),
    ('huang2','zizhou11'),
    ('huang3','zizhou10'),
    ('huang4','zizhou9'),
    ('huang5','zizhou8'),
    ('huang6','zizhou7'),
    ('huang7','zizhou6'),
    ('huang8','zizhou5'),
    ('huang9','zizhou4'),
    ('huang10','zizhou3'),
    ('huang11','zizhou2'),
    ('huang12','zizhou1'),
]
#提交批量
curs.executemany(mysqlinsert,us)
conn.commit()
# 查询语句
mysqlselect="""
    select*from users
"""
#执行查询语句后不需要进行commit
curs.execute(mysqlselect)
#遍历结果
for row in curs:
    print  row
# 关闭连接，使用链接名进行操作
conn.close()

直接用sql语句操作数据库

通过ORM方式操作数据库

　　SQLAlchemy：the python sql toolkit and object relational mapper

　　　　- http://www.sqlalchemy.org

　　　　pip install SQLAlchemy 

ORM：object relational mapper

　　表映射到类

　　行映射到对象

　　列映射到属性

 

程序的分层架构：

　　三层、4层、多层

　　表示层、业务逻辑层、持久化层、数据库层

　　表示层、业务逻辑层、数据库层

分层的特点：

　　伸缩性、重用、维护

sqlalchemy的有点

隐藏数据库实现，业务代码操作对象而不是数据库

开发效率高，无需编写sql代码就可完成数据库操作

良好的数据库操作接口、简单、学习成本低

动态数据表映射

 

缺点：不容易做数据库查询优化，可能带来性能上的损失

 

开发步骤：

　　创建连接

　　创建映射

　　初始化映射实例

　　创建会话

　　持久化对象实例　　增删改查

创建连接

　　engine=create_engine(数据库连接串,echo=True)

　　　　#echo=True，表示打印orm中的sql执行情况

定义映射

　　声明基类：

　　　　Base=declarative_base()

　　声明队形和实体映射类，继承基类

　　　　class User(Base):

　　创建表

　　　　Base.metadata.create_all(engine)

　　创建会话

　　　　Session=sessionmaker(bind=engine)

　　　　session=Session()

　　会话方法

　　　　session.add()/add_all()

　　　　session.delete()

　　　　session.commit()

　　　　session.query()

　　　　　　filter()/filter_by()

　　　　　　order_by()

　　　　　　first()

　　　　　　all()

　　　　　　切片操作([1,4])

 

# !/usr/bin/python
# encoding:utf8


from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DateTime,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship,backref

# 1、创建连接
engine=create_engine('mysql+mysqlconnector://root:@127.0.0.1:3306/test?charset=utf8',echo=True)

# 2、定义映射
Base=declarative_base()

class User(Base):
    __tablename__='users'
    id=Column(Integer,primary_key=True)
    name=Column(String(40))
    address=Column(String(100))

    def __str__(self):
        return "User(id={0},name={1},address={2})".format(self.id,self.name,self.address)

# 3、映射实例化，创建数据库表
Base.metadata.create_all(engine)

# 4、创建会话
Session=sessionmaker(engine)
session=Session()
# 5、操作数据
u1=User(name='zhangsan',address='beijing')
session.add(u1)
session.commit()
print u1.id

us=[
    ('huang1','zizhou12'),
    ('huang2','zizhou11'),
    ('huang3','zizhou10'),
    ('huang4','zizhou9'),
    ('huang5','zizhou8'),
    ('huang6','zizhou7'),
    ('huang7','zizhou6'),
    ('huang8','zizhou5'),
    ('huang9','zizhou4'),
    ('huang10','zizhou3'),
    ('huang11','zizhou2'),
    ('huang12','zizhou1'),
]
for u in us:
    u2=User(name=u[0],address=u[1])
    session.add(u2)
session.commit()

uc=session.query(User).filter(User.name.like('h%')).all()
for uc1 in uc:
    print uc1
uc2=session.query(User).filter(User.name=='zhangsan').all()
# uc2=session.query(User).filter(User.name=='zhangsan').first()
for uc21 in uc2:
    print uc21
    uc21.address = 'jiangnan'
    print uc21


session.commit()
for uc21 in uc2:
    session.delete(uc21)
session.commit()

 MongoDB

https://www.mongodb.org/dl/win32

www.mongodb.com

www.mongovue.com

https://pypi.python.org/pypi/pymongo

　　pip install pymongo

启动：

　　安装版：bin目录下，已存在文件夹，执行：mongod --dbpath 具体的path，不需要指定端口

　　mongod --port27017 --dbpath ./data --logpath ./log/mongod.log -logappend

　　　　.\bin\mongod --dbpath .\data --logpath .\log\mongod.log -logappend

　　　　mongod –storageEngine mmapv1 –dbpath XXXXX

　　　　貌似有时候不需要指定端口

　　在浏览器下打开:http://localhost:27017/

常见操作

　　bin目录下

　　mongo

　　db

　　use yourdb

　　show dbs

　　show collections

　　db.collection.insert()
　　　　db.zhangdb.users.insert({'name':'zhang','sex':'m'})

　　db.collection.find()

　　db.collection.remove()

　　　　删除
　　　　db.zhangdb.users.remove({'name':'zhang'})

　　db.collection.update()

　　　　db.users.update({'name':'zhang'},{$set{'age':20}})
　　db.collection.drop()

pymongo

　　insert_one(doc)

　　insert_many(doc,ordered=True、False)  False并行插入数据库，中间存在错误后面的插入动作也可继续进行。True串行操作，如果中间出现失败情况，后续的不会在进行插入了。

　　软事物：文档基本保障事物完整性，文档由key-value对存在。

查询

　　find(filter)

更新操作

　　update_one(filter,update,upsert=True/False)

　　update_many(filter,update,upsert=True/False)

 替换操作

　　replace_one(filter,replacement,upsert=True/False)

删除

　　remove(filter)

　　delete_one(filter)

　　delete_many(filter)

# !/usr/bin/python
# encoding:utf8

"""
@author:hj
@contact:huangjie01@fangdd.com
@file: pymongotest.py
@time: 2017/2/23 11:46
"""
import pymongo
from pymongo import MongoClient
conn=MongoClient('localhost')
db=conn.testerdb
users=db.app.users
#插入
# u1={
#     'name':'zhang',
#     'mails':["s@s", 'fkk@dgdfg.com']
# }
# x=users.insert_one(u1)
# print dir(x)
# print x.inserted_id
#
# props=['name','mails']
# us=[
#     ['zhao',['abc@we','sdfsld@kdfsd']],
#     ['zhao',['abc@we','sdfsld@kdfsd']],
#     ['zhao',['abc@we','sdfsld@kdfsd']],
#     ['zhao',['abc@we','sdfsld@kdfsd']],
#     ['zhao',['abc@we','sdfsld@kdfsd']],
#     ['zhao',['abc@we','sdfsld@kdfsd']],
#     ['zhao',['abc@we','sdfsld@kdfsd']],
#     ['zhao',['abc@we','sdfsld@kdfsd']]
# ]
# ul=[dict(zip(props,u))for u in us]
# print ul
# users.insert_many(ul,ordered=False)
#
# # 查询
# import json
# # json和bson中间的转换
# from bson import json_util
# #全局扫描
# cursor=users.find()
# #等值扫描
# cursor=users.find({'name':'zhang'})
# #in
# cursor=users.find({'name':{'$in':{'zhang','li'}}})
# #大于
# cursor=users.find({'age':{'$gt':25}})
# # and
# cursor=users.find({'name':{'$in':['zhang','li']},
#                    'age':{'$gt':25}
#                    }
#                   )
# cursor=users.find({'name':{'$in':['zhang','li']},
#                    'mails':{'$size':2}
#                    }
#                   )
# # or
# cursor=users.find({'$or':[{'name':{'$in':['zhang','li']}},
#                           {'age':{'$gt':25}
#                            }
#                           ]
#                    }
#                   )
# for user in cursor:
#     print user
#     print json.dumps(user,indent=4,default=json_util.default)
##  更新
## 新增
# users.update_many(
#     {},
#     {'$set':{"age":28}}
#     # {'$unset': {"age": 28}}
# )
## 所有加2
# users.update_many(
#     {},
#     {'$inc':{"age":2}}
# )
# #更新指定的数据，age和20比较去叫小的
# users.update_many(
#     {'name':
#          {'$in':['zhang','li']}
#      },
#     {'$min':
#          {'age':20}
#     }
# )
users.update_many(
    {'name':
         {'$in':['zhang','li']}
     },
    {'$currentDate':
         {'create_time':True,
          # 自然日
          'mod_time':{'$type':'timestamp'}
          #时间戳
          }
    }
)

 

集合操作

　　等价于关系数据库的分组汇总统计，比如分组、最大值、最小值

　　如同linux管道：cat a.txt|wc -l

　　管道操作符

　　　　collection.aggregate([{stage1},{stag1},....])

　　表达式操作符