sqlalchemy mysql创建表_Python中使用SQLAlchemy连接Mysql数据库（单表操作）

一，SQLAlchemy的安装 使用 $ easy_install sqlalchemy或 $ pip install sqlalchemy 如果出现什么错，就进去root用户下进行安装试试，或者网上查查 import sqlalchemy 这样说明成功了，切记是小写哦 二，使用 理论我也不懂，自己查查资料，现在用一个小的案

一，SQLAlchemy的安装

使用

$easy_install sqlalchemy

或

$pip install sqlalchemy

如果出现什么错，就进去root用户下进行安装试试，或者网上查查

>>>import sqlalchemy

>>>

这样说明成功了，切记是小写哦

二，使用

理论我也不懂，自己查查资料，现在用一个小的案例说一下使用步骤

1，在进行数据操作之前要先连上数据库。

>>>from sqlalchemy import create_engine

>>>from sqlalchemy.orm import sessionmaker

>>>DB_CONNECT = 'mysql+mysqldb://root:102@localhost/mydb'

>>>engine = create_engine(DB_CONNECT, echo=True)

>>>DB_Session = sessionmaker(bind=engine)

>>>session = DB_Session()

from 是从sqlalchemy中插入必须的模板，DB_CONNECT 是构造数据库的路径 ，mysql+mysqldb是说明使用MySQL-Python 来连接，root是数据库用户名，102是密码，localhost表示是数据库在本机上，mydb是要连接的数据库名字，设置字符集的charset可以省了

create_engine() 会返回一个数据库引擎，echo 参数为 True 时，会显示每条执行的 SQL 语句，生产环境下可关闭。

sessionmaker(bind=engine)会生成一个数据库会话类。这个类的实例可以当成一个数据库连接，它同时还记录了一些查询的数据，并决定什么时候执行 SQL 语句。由于 SQLAlchemy 自己维护了一个数据库连接池(默认 5 个连接)，也可以自己设置。

得到session 后，就可以执行 SQL 了：

2，在进行操作前先把表给建立了，由于SQLAlchemy 可以和变进行建立连接并且可以通过语言进行见表

mysql> show tables;

Empty set (0.00 sec)

mysql>

此时是没有表的，现在我们建立一个学生便stu，一个课程表cla和一个成绩表grade

>>> from sqlalchemy import Column

>>> from sqlalchemy.types import CHAR, Integer, String

>>> from sqlalchemy.ext.declarative import declarative_base

>>> from random import randint

>>> from sqlalchemy import ForeignKey

>>> BaseModel = declarative_base()

>>> def init_db():

... BaseModel.metadata.create_all(engine)

...

>>> def drop_db():

... BaseModel.metadata.drop_all()

...

>>> class Stu(BaseModel):

... __tablename__='stu'

... id = Column(Integer,primary_key = True)

... name = Column(CHAR(30))

...

>>> class Cla(BaseModel):

... __tablename__='cla'

... id = Column(Integer,primary_key = True)设置主键

... cname = Column(CHAR(30))

...

>>> class Grade(BaseModel):

... __tablename__ = 'grade'

... uid = Column(Integer,ForeignKey('stu.id'))设置外键

... cid = Column(Integer,ForeignKey('cla.id'))

... id = Column(Integer,primary_key=True)

... gre=Column(Integer)

...

declarative_base() 创建了一个 BaseModel 类，这个类的子类可以自动与一个表关联。以 Stu 类为例，它的 tablename 属性就是数据库中该表的名称，它有 id 和 name 这两个字段，分别为整型和 30 个定长字符。Column 还有一些其他的参数，我就不解释了。

最后，BaseModel.metadata.create_all(engine) 会找到 BaseModel 的所有子类，并在数据库中建立这些表；drop_all() 则是删除这些表。

现在执行init_db()进行建立表，对应语句如下

>>> init_db()

CREATE TABLE stu (

id INTEGER NOT NULL AUTO_INCREMENT,

name CHAR(30),

PRIMARY KEY (id)

)

CREATE TABLE cla (

id INTEGER NOT NULL AUTO_INCREMENT,

cname CHAR(30),

PRIMARY KEY (id)

)

CREATE TABLE grade (

id INTEGER NOT NULL AUTO_INCREMENT,

uid INTEGER,

cid INTEGER,

gre INTEGER,

PRIMARY KEY (id),

FOREIGN KEY(uid) REFERENCES stu (id),

FOREIGN KEY(cid) REFERENCES cla (id)

)

COMMIT

>>>

以上就是执行时对应的建表语句，现在去数据库看看表是否存在，并查看一个表结构

mysql> show tables;

+----------------+

| Tables_in_mydb |

+----------------+

| cla |

| grade |

| stu |

+----------------+

3 rows in set (0.00 sec)

表已经建立成功了，现在看一下表结构

mysql> desc grade;

+-------+---------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| uid | int(11) | YES | MUL | NULL | |

| cid | int(11) | YES | MUL | NULL | |

| gre | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

可以看出 使用SQLAlchemy中的语句和使用SQL语句的结果一样。接下来就可以插入数据了

>>>stu = Stu(name='a')

>>>session.add(stu)

>>>stu = Stu(name='b')

>>>session.add(stu)

>>>stu = Stu(name='c')

>>>session.add(stu)

>>>stu = Stu(name='d')

>>>session.add(stu)

>>>stu = Stu(name='e')

>>>session.add(stu)

>>>

手动插入了五条记录，但此时还没有提交，没有真正的写入数据库

或者使用非ORM方式进行插入

>>>session.execute(Stu.__table__.insert(),[{'name':randint(1,100)} for i in xrange(10000)])

>>>session.commit()

#可以速度更快的插入更多的数据

>>> session.commit()

2016-05-09 18:22:16,839 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2016-05-09 18:22:16,840 INFO sqlalchemy.engine.base.Engine INSERT INTO stu (name) VALUES (%s)

2016-05-09 18:22:16,840 INFO sqlalchemy.engine.base.Engine ('a',)

2016-05-09 18:22:16,841 INFO sqlalchemy.engine.base.Engine INSERT INTO stu (name) VALUES (%s)

2016-05-09 18:22:16,841 INFO sqlalchemy.engine.base.Engine ('b',)

2016-05-09 18:22:16,841 INFO sqlalchemy.engine.base.Engine INSERT INTO stu (name) VALUES (%s)

2016-05-09 18:22:16,841 INFO sqlalchemy.engine.base.Engine ('c',)

2016-05-09 18:22:16,842 INFO sqlalchemy.engine.base.Engine INSERT INTO stu (name) VALUES (%s)

2016-05-09 18:22:16,842 INFO sqlalchemy.engine.base.Engine ('d',)

2016-05-09 18:22:16,842 INFO sqlalchemy.engine.base.Engine INSERT INTO stu (name) VALUES (%s)

2016-05-09 18:22:16,842 INFO sqlalchemy.engine.base.Engine ('e',)

2016-05-09 18:22:16,843 INFO sqlalchemy.engine.base.Engine COMMIT

>>>

此时真的写入数据库了哦。向课程表插入五条

>>>cla = Cla(cname='yuwen')

>>>session.add(cla)

>>>cla = Cla(cname='shuxue')

>>>session.add(cla)

>>>cla = Cla(cname='yingyu')

>>>session.add(cla)

>>>cla = Cla(cname='wuli')

>>>session.add(cla)

>>>cla = Cla(cname='huaxue')

>>>session.add(cla)

>>>session.commit()

3,现在开始操作数据

>>> query = session.query(Stu)

>>> for st in query:

... print st.name

...

对应的SQL语句

SELECT stu.id AS stu_id, stu.name AS stu_name

FROM stu

2016-05-09 18:56:07,084 INFO sqlalchemy.engine.base.Engine ()

a

b

c

d

e

>>> print query.all()# # 返回的是一个类似列表的对象

SELECT stu.id AS stu_id, stu.name AS stu_name

FROM stu

2016-05-09 18:58:16,085 INFO sqlalchemy.engine.base.Engine ()

[object at 0xb66b3f4c>, object at 0xb5e4202c>, object at 0xb66b3f8c>, object at 0xb5e4206c>, object at 0xb6688c0c>]

>>> print query.first().name# 有数据时返回第一条记录，没有数据时会返回 None

SELECT stu.id AS stu_id, stu.name AS stu_name

FROM stu

LIMIT %s

2016-05-09 18:59:43,149 INFO sqlalchemy.engine.base.Engine (1,)

a

# print query.one().name# 不存在，或有多行记录时会抛出异常

>>> print query.filter(Stu.id == 2).first().name

SELECT stu.id AS stu_id, stu.name AS stu_name

FROM stu

WHERE stu.id = %s

LIMIT %s

2016-05-09 19:04:54,363 INFO sqlalchemy.engine.base.Engine (2, 1)

b

>>> print query.filter('id = 2').first().name # 支持字符串

SELECT stu.id AS stu_id, stu.name AS stu_name

FROM stu

WHERE id = 2

LIMIT %s

2016-05-09 19:07:02,016 INFO sqlalchemy.engine.base.Engine (1,)

b

>>> print query.get(2).name # 以主键获取，等效于上句

2016-05-09 19:07:40,007 INFO sqlalchemy.engine.base.Engine SELECT stu.id AS stu_id, stu.name AS stu_name

FROM stu

WHERE stu.id = %s

2016-05-09 19:07:40,007 INFO sqlalchemy.engine.base.Engine (2,)

b

>>> print query.get(2).id

SELECT stu.id AS stu_id, stu.name AS stu_name

FROM stu

WHERE stu.id = %s

2016-05-09 19:08:46,009 INFO sqlalchemy.engine.base.Engine (2,)

2

>>> quer2 = session.query(Stu.name)

>>> print quer2.all()

SELECT stu.name AS stu_name

FROM stu

2016-05-09 19:09:46,259 INFO sqlalchemy.engine.base.Engine ()

[('a',), ('b',), ('c',), ('d',), ('e',)]

>>> print quer2.limit(1).all() #只返回一条

2016-05-09 19:11:23,383 INFO sqlalchemy.engine.base.Engine SELECT stu.name AS stu_name

FROM stu

LIMIT %s

2016-05-09 19:11:23,383 INFO sqlalchemy.engine.base.Engine (1,)

[('a',)]

>>> print quer2.limit(2).all()#只返回两条

SELECT stu.name AS stu_name

FROM stu

LIMIT %s

2016-05-09 19:11:29,480 INFO sqlalchemy.engine.base.Engine (2,)

[('a',), ('b',)]

>>> print quer2.offset(1).all() #跳过一条，从第二条数据开始查询

SELECT stu.name AS stu_name

FROM stu

LIMIT %s, 18446744073709551615

2016-05-09 19:13:25,734 INFO sqlalchemy.engine.base.Engine (1,)

[('b',), ('c',), ('d',), ('e',)]

>>> print quer2.offset(3).all() #从第四条数据开始

SELECT stu.name AS stu_name

FROM stu

LIMIT %s, 18446744073709551615

2016-05-09 19:13:39,629 INFO sqlalchemy.engine.base.Engine (3,)

[('d',), ('e',)]

#按name降序排序

>>> print quer2.order_by(Stu.name.desc()).all()

SELECT stu.name AS stu_name

FROM stu ORDER BY stu.name DESC

2016-05-09 19:16:56,022 INFO sqlalchemy.engine.base.Engine ()

[('e',), ('d',), ('c',), ('b',), ('a',)]

>>> print quer2.order_by('name desc').all()

SELECT stu.name AS stu_name

FROM stu ORDER BY name desc

2016-05-09 19:17:09,851 INFO sqlalchemy.engine.base.Engine ()

[('e',), ('d',), ('c',), ('b',), ('a',)]

#按name降序，有重复的按id升序排序

>>> print session.query(Stu.id).order_by('name desc','id').all()

SELECT stu.id AS stu_id

FROM stu ORDER BY name desc, stu.id

2016-05-09 19:20:34,818 INFO sqlalchemy.engine.base.Engine ()

[(5L,), (4L,), (3L,), (2L,), (1L,)]

#scalar()在有多条数据时使用报出异常,all()可以使用多条也可以使用一条

#>>> print quer2.filter(Stu.id>2).scalar()

>>> print quer2.filter(Stu.id>2).all()

SELECT stu.name AS stu_name

FROM stu

WHERE stu.id > %s

2016-05-09 19:56:47,760 INFO sqlalchemy.engine.base.Engine (2,)

[('c',), ('d',), ('e',)]

>>> print quer2.filter(Stu.id==2).all()

SELECT stu.name AS stu_name

FROM stu

WHERE stu.id = %s

2016-05-09 19:57:47,901 INFO sqlalchemy.engine.base.Engine (2,)

[('b',)]

>>> print quer2.filter(Stu.id==2).scalar()

SELECT stu.name AS stu_name

FROM stu

WHERE stu.id = %s

2016-05-09 19:23:38,761 INFO sqlalchemy.engine.base.Engine (2,)

b

>>> print quer2.filter('id=2').scalar()

SELECT stu.name AS stu_name

FROM stu

WHERE id=2

2016-05-09 19:43:47,797 INFO sqlalchemy.engine.base.Engine ()

b

#在此中‘，’等价于and

>>> print query2.filter(Stu.id>1,Stu.name !='a').first()

SELECT stu.name AS stu_name

FROM stu

WHERE stu.id > %s AND stu.name != %s

LIMIT %s

2016-05-09 19:51:14,571 INFO sqlalchemy.engine.base.Engine (1, 'a', 1)

('b',)

>>>

#此种迭代也类似与and

>>> query3 = query2.filter(Stu.id>1)

>>> query3 = query3.filter(Stu.name != 'a')

>>> query3.first()

2016-05-09 19:53:50,150 INFO sqlalchemy.engine.base.Engine SELECT stu.name AS stu_name

FROM stu

WHERE stu.id > %s AND stu.name != %s

LIMIT %s

2016-05-09 19:53:50,151 INFO sqlalchemy.engine.base.Engine (1, 'a', 1)

('b',)

#or_就是类似or

>>> print query2.filter(or_(Stu.id == 1,Stu.id==2)).all()

2016-05-09 19:55:59,383 INFO sqlalchemy.engine.base.Engine SELECT stu.name AS stu_name

FROM stu

WHERE stu.id = %s OR stu.id = %s

2016-05-09 19:55:59,383 INFO sqlalchemy.engine.base.Engine (1, 2)

[('a',), ('b',)]

# in的用法

>>> print query2.filter(Stu.id.in_((1,2,3))).all()

SELECT stu.name AS stu_name

FROM stu

WHERE stu.id IN (%s, %s, %s)

2016-05-09 20:01:01,729 INFO sqlalchemy.engine.base.Engine (1, 2, 3)

[('a',), ('b',), ('c',)]

>>>

#为null的一些用法

>>>query4 = session.query(Stu.id)

>>>print query4.filter(Stu.name==None).scalar()

SELECT stu.id AS stu_id

FROM stu

WHERE stu.name IS NULL

2016-05-09 20:02:59,821 INFO sqlalchemy.engine.base.Engine ()

None

>>>

>>>print query4.filter('name is null').scalar()

SELECT stu.id AS stu_id

FROM stu

WHERE name is null

2016-05-09 20:03:40,312 INFO sqlalchemy.engine.base.Engine ()

None

>>>

#不为null的一些用法

>>> print query4.filter(not_(Stu.name == None)).all()

SELECT stu.id AS stu_id

FROM stu

WHERE stu.name IS NOT NULL

2016-05-09 20:04:49,888 INFO sqlalchemy.engine.base.Engine ()

[(1L,), (2L,), (3L,), (4L,), (5L,)]

>>>

>>> print query4.filter(Stu.name != None).all()

SELECT stu.id AS stu_id

FROM stu

WHERE stu.name IS NOT NULL

2016-05-09 20:05:42,724 INFO sqlalchemy.engine.base.Engine ()

[(1L,), (2L,), (3L,), (4L,), (5L,)]

>>>

#func条用各种函数的用法

>>> print query4.count()

SELECT count(*) AS count_1

FROM (SELECT stu.id AS stu_id

FROM stu) AS anon_1

2016-05-09 20:08:43,352 INFO sqlalchemy.engine.base.Engine ()

5

>>> print session.query(func.count('*')).select_from(Stu).scalar()

SELECT count(%s) AS count_1

FROM stu

2016-05-09 20:08:43,356 INFO sqlalchemy.engine.base.Engine ('*',)

5

>>> print session.query(func.count('1')).select_from(Stu).scalar()

SELECT count(%s) AS count_1

FROM stu

2016-05-09 20:08:43,362 INFO sqlalchemy.engine.base.Engine ('1',)

5

>>> print session.query(func.count(Stu.id)).scalar()

SELECT count(stu.id) AS count_1

FROM stu

2016-05-09 20:08:43,369 INFO sqlalchemy.engine.base.Engine ()

5

>>> print session.query(func.count('*')).filter(Stu.id > 0).scalar() # filter()中包含Stu，因此不需要指定表

SELECT count(%s) AS count_1

FROM stu

WHERE stu.id > %s

2016-05-09 20:08:43,377 INFO sqlalchemy.engine.base.Engine ('*', 0)

5

>>> print session.query(func.count('*')).filter(Stu.name == 'a').limit(1).scal() == 1 # 可以用 limit() 限制 count() 的返回数

SELECT count(%s) AS count_1

FROM stu

WHERE stu.name = %s

LIMIT %s

2016-05-09 20:08:43,394 INFO sqlalchemy.engine.base.Engine ('*', 'a', 1)

True

>>> print session.query(func.sum(Stu.id)).scalar()

SELECT sum(stu.id) AS sum_1

FROM stu

2016-05-09 20:08:43,401 INFO sqlalchemy.engine.base.Engine ()

15

>>> print session.query(func.now()).scalar() # func 后可以跟任意函数名，只要该据库支持

SELECT now() AS now_1

2016-05-09 20:08:43,406 INFO sqlalchemy.engine.base.Engine ()

2016-05-09 20:08:43

>>> print session.query(func.current_timestamp()).scalar()

SELECT CURRENT_TIMESTAMP AS current_timestamp_1

2016-05-09 20:08:43,411 INFO sqlalchemy.engine.base.Engine ()

2016-05-09 20:08:43

>>> print session.query(func.md5(Stu.name)).filter(Stu.id == 1).scalar()

SELECT md5(stu.name) AS md5_1

FROM stu

WHERE stu.id = %s

2016-05-09 20:08:44,841 INFO sqlalchemy.engine.base.Engine (1,)

0cc175b9c0f1b6a831c399e269772661

>>>

#修改数据

>>> query.filter(Stu.id==1).update({Stu.name:'li'})

UPDATE stu SET name=%s WHERE stu.id = %s

2016-05-09 20:12:57,027 INFO sqlalchemy.engine.base.Engine ('li', 1)

1L

#删除数据

>>> query = session.query(Grade)

>>> query.filter(Grade.id == 1).delete()

DELETE FROM grade WHERE grade.id = %s

2016-05-09 20:28:18,638 INFO sqlalchemy.engine.base.Engine (1,)

1L

>>>

此时没有提交，在数据库中环视存在的

mysql> select * from grade;

+----+------+------+------+

| id | uid | cid | gre |

+----+------+------+------+

| 1 | 1 | 1 | 60 |

| 2 | 2 | 1 | 66 |

| 3 | 5 | 1 | 66 |

| 4 | 5 | 5 | 96 |

| 5 | 5 | 3 | 96 |

| 6 | 3 | 2 | 96 |

| 7 | 3 | 4 | 76 |

| 8 | 4 | 4 | 76 |

| 9 | 4 | 3 | 76 |

| 10 | 4 | 5 | 76 |

| 11 | 1 | 4 | 76 |

| 12 | 1 | 5 | 76 |

| 13 | 2 | 5 | 76 |

| 14 | 3 | 3 | 60 |

| 15 | 2 | 3 | 50 |

+----+------+------+------+

15 rows in set (0.00 sec)

#开始提交

>>> session.commit()

2016-05-09 20:31:02,461 INFO sqlalchemy.engine.base.Engine COMMIT

>>>

mysql> select * from grade;

+----+------+------+------+

| id | uid | cid | gre |

+----+------+------+------+

| 2 | 2 | 1 | 66 |

| 3 | 5 | 1 | 66 |

| 4 | 5 | 5 | 96 |

| 5 | 5 | 3 | 96 |

| 6 | 3 | 2 | 96 |

| 7 | 3 | 4 | 76 |

| 8 | 4 | 4 | 76 |

| 9 | 4 | 3 | 76 |

| 10 | 4 | 5 | 76 |

| 11 | 1 | 4 | 76 |

| 12 | 1 | 5 | 76 |

| 13 | 2 | 5 | 76 |

| 14 | 3 | 3 | 60 |

| 15 | 2 | 3 | 50 |

+----+------+------+------+

14 rows in set (0.00 sec)

也获取不到对象了

>>> print query.get(1)

SELECT grade.id AS grade_id, grade.uid AS grade_uid, grade.cid AS grade_cid, grade.gre AS grade_gre

FROM grade

WHERE grade.id = %s

2016-05-09 20:32:20,742 INFO sqlalchemy.engine.base.Engine (1,)

None

>>>

单表的增删改查完事了，下面来看看多表连接操作

http://blog.csdn.net/u011573853/article/details/51363780

一些细节会在下面进行说明(事务，加锁，编码等)

http://blog.csdn.net/u011573853/article/details/51366124

本文原创发布php中文网，转载请注明出处，感谢您的尊重！