Python

来源：互联网发布：u盘低格后数据恢复编辑：程序博客网时间：2024/06/06 08:05

一、简介

SQLAlchemy是构建在WSGI规范下的，Python企业级持久性模型，支持ORM（Object Relational Mappers，ORM）。

二、安装

环境：CentOS6.5

pip install sqlalchemyyum install python-devel mysql-devel zlib-devel openssl-devel  -ypip install mysql-pythonpip install --egg mysql-connector-python-rf

如果切换源，使用 -i 参数，如：

 -i https://pypi.tuna.tsinghua.edu.cn/simple

其中-i是切换源，附几个国内源

pypi.douban.com
pypi.hustunique.com
pypi.gocept.com
pypi.tuna.tsinghua.edu.cn

也可以到
https://pypi.python.org/pypi/MySQL-python/1.2.5#downloads
http://www.codegood.com/download/11/
下载安装

官网
http://www.sqlalchemy.org/

如果上面安装的某个包有问题，比较容易的方式是去搜索其whl包，下载以后在对应目录直接使用命令：pip install 文件名 安装。

另外需要mysqlclient到 https://dev.mysql.com/downloads/connector/python/ 下载

三、准备数据库、表

/etc/my.ini
注释掉 #skip-name-resolve

建表
在test数据库建user表，字段：id,name

CREATE TABLE IF NOT EXISTS `user` (  `id` int(11) NOT NULL,  `name` varchar(32) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

操作语句

#!/usr/bin/python#-*-coding:utf-8-*-from sqlalchemy import Column, String, create_enginefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_base# 创建对象的基类:Base = declarative_base()# 定义User对象:class User(Base):    # 表的名字:    __tablename__ = 'user'    # 表的结构:    id = Column(String(20), primary_key=True)    name = Column(String(20))# 初始化数据库连接:engine = create_engine('mysql+mysqlconnector://test:test@localhost:3306/test')# 创建DBSession类型:DBSession = sessionmaker(bind=engine)# 创建session对象:session = DBSession()# 创建新User对象:new_user = User(id='5', name='Bob')# 添加到session:session.add(new_user)# 提交即保存到数据库:session.commit()# 关闭session:session.close()

建表示例

# -*- coding: utf-8 -*-from flask import Flask#from flask.ext.sqlalchemy import SQLAlchemyfrom flask_sqlalchemy import SQLAlchemyfrom sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey# 连接数据库 engine = create_engine("mysql+mysqlconnector://adspy:adspy1$$3456@localhost:3306/test?charset=utf8",encoding="utf-8", echo=True)# 获取元数据metadata = MetaData()# 定义表user = Table('user1', metadata,    Column('id', Integer, primary_key=True),    Column('name', String(20)),    Column('fullname', String(40)),  )address = Table('address1', metadata,    Column('id', Integer, primary_key=True),    Column('user_id', None, ForeignKey('user1.id')),    Column('email', String(60), nullable=False)  )# 创建数据表，如果数据表存在，则忽视metadata.create_all(engine)# 获取数据库连接conn = engine.connect()

建表与插入

# -*- coding: utf-8 -*-from flask import Flaskfrom sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey# 连接数据库 engine = create_engine("mysql+mysqlconnector://user:pass@localhost:3306/test?charset=utf8",encoding="utf-8", echo=True)# 获取元数据metadata = MetaData()# 定义表user = Table('user1', metadata,    Column('id', Integer, primary_key=True),    Column('name', String(20)),    Column('fullname', String(40)),  )address = Table('address1', metadata,    Column('id', Integer, primary_key=True),    Column('user_id', None, ForeignKey('user1.id')),    Column('email', String(60), nullable=False)  )# 创建数据表，如果数据表存在，则忽视metadata.create_all(engine)# 获取数据库连接conn = engine.connect()i = user.insert()u = dict(name='jack',fullname='jackJone')r = conn.execute(i,**u)r.inserted_primary_keyi = address.insert()addresses = [{'user_id':1,'email':'abc@sohu.com'}]r = conn.execute(i,addresses)print(r.rowcount)i = user.insert().values(name='tom',fullname='tom jim')i.compile()print(i.compile())# INSERT INTO user1 (name, fullname) VALUES (:name, :fullname)print(i.compile().params)# {'fullname': 'tom jim', 'name': 'tom'}r = conn.execute(i)print(r.rowcount)

查询

# -*- coding: utf-8 -*-from flask import Flaskfrom sqlalchemy import create_engine, Table,select, Column, Integer, String, MetaData, ForeignKey# 连接数据库 engine = create_engine("mysql+mysqlconnector://adspy:adspy1$$3456@localhost:3306/test?charset=utf8",encoding="utf-8", echo=True)# 获取元数据metadata = MetaData()# 定义表user = Table('user1', metadata,    Column('id', Integer, primary_key=True),    Column('name', String(20)),    Column('fullname', String(40)),  )address = Table('address1', metadata,    Column('id', Integer, primary_key=True),    Column('user_id', None, ForeignKey('user1.id')),    Column('email', String(60), nullable=False)  )# 获取数据库连接conn = engine.connect()s = select([user])print(s)r = conn.execute(s)ru = r.fetchall() print("查询结果")print(ru)r.closed  # 只要 r.fetchall() 之后，就会自动关闭 ResultProxy 对象# 表的字段print("表的字段")print(user.c)

参考：
http://www.jb51.net/article/86018.htm

部分内容：
http://www.liaoxuefeng.com/wiki/001374738125095c955c1e6d8bb493182103fac9270762a000/0014021031294178f993c85204e4d1b81ab032070641ce5000

阅读全文

0 0