python通过elixir包操作mysql数据库实例代码

yipeiwu_com7年前Python基础

本文研究的主要是python通过elixir包操作mysql数据库的相关实例,具体如下。

python操作数据库有很多方法,下面介绍elixir来操作数据库。elixir是对sqlalchemy lib的一个封装,classes和tables是一一对应的,能够一步定义classes,tables和mappers,支持定义多个primary key。

定义model.py

from elixir import sqlalchemy 
from elixir import * 
 
engine =sqlalchemy.create_engine('mysql://root:root@localhost/') #the first root is the user, and the sencond root is the password 
#engine.execute("DROP DATABASE IF EXISTS elixir") 
engine.execute("CREATE DATABASE IF NOT EXISTS elixir") 
 
 
metadata.bind='mysql://root:root@localhost:3306/elixir' 
#metadata.bind.echo =True 
class Movie(Entity): 
  using_options(tablename='movies') 
 
  title = Field(Unicode(30),primary_key = True) 
  year = Field(Integer, primary_key = True) 
  description = Field(UnicodeText) 
  director = ManyToOne('Director') 
  genres = ManyToMany('Genre') 
  actor = ManyToMany('Actor') 
 
  def __repr__(self): 
    return '<Move "%s" (%d)>' % (self.title, self.year) 
 
class Person(Entity): 
  using_options(inheritance='multi') 
  using_options(tablename='person') 
 
  name = Field(Unicode(60)) 
 
  def __repr__(self): 
    return '<Person "%s">' % self.name 
 
 
class Director(Person): 
  using_options(inheritance='multi') 
  using_options(tablename='director') 
 
  movies = OneToMany('Movie') 
 
  def __repr__(self): 
    return '<Director "%s">' % self.name 
 
class Genre(Person): 
  using_options(inheritance='multi') 
  using_options(tablename='genre') 
 
  movies = ManyToMany('Movie') 
 
  def __repr__(self): 
    return '<Genre "%s">' % self.name 
 
class Actor(Person): 
  using_options(inheritance='multi') 
  using_options(tablename='actor') 
 
  movies = ManyToMany('Movie') 
 
  def __repr__(self): 
    return '<Actor "%s">' % self.name 

model_test.py

from model import * 
 
# setup_all(True) is equal to the following two staps: 
setup_all() # create sqlalchemy table object as mapper object for the class 
create_all() # take all table objcts and create real tables by issuing SQL statements on the databse. 
 
Actor1 = Actor(name=u"lvliang") 
scifi = Genre(name = u"Science-Fiction") 
rscott = Director(name = u"Ridley Scott") 
glucas = Director(name = u"George Lucas") 
alien = Movie(title = u"Alien", year = 1979, director=rscott, genres=[scifi, Genre(name=u"Horror")], actor = [Actor1]) 
brunner = Movie(title = u"Blade Runner", year = 1982, director = rscott, genres=[scifi]) 
swars = Movie(title = u"Star Wars", year = 1977, director = glucas, genres=[scifi]) 
session.commit() 
 
 
m1 = Movie.query.filter_by(title=u"Alien").one() 
m2 = Movie.query.filter(Movie.year>1980).all() 
m3 = Movie.query.filter(Movie.director.has(name = u"Ridley Scott")).all() 
m4 = Movie.query.filter(Movie.director.has(Director.name.endswith(u"Scott"))).all() 
m5 = Movie.query.filter(Movie.genres.any(name = u"Horror")).all() 
 
print m1 
print m2 
print m3 
print m4 
print m5 
 
d = Director.get_by(name = u"Ridley Scott") # Class.get_by(xxx) is a shortcut for Class.query.filter_by(xxx).first 
q = Movie.query.filter_by(director = d) #get all movies directed by director d 
m = q.filter_by(year = 1979).all() 
print "Movie direct by %s in year 1979 are " %(d.name) 
print m 
 
movies = q.order_by(sqlalchemy.desc(Movie.year)).all() 
print movies 
fro m in movies: 
  m.delete() 
session.commit() 

执行model.py,结果为:

查看数据库,结果为:

总结

以上就是本文关于python通过elixir包操作mysql数据库实例代码的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站其他相关专题,如有不足之处,欢迎留言指出。感谢朋友们对本站的支持!

相关文章

Python装饰器的函数式编程详解

Python的装饰器的英文名叫Decorator,当你看到这个英文名的时候,你可能会把其跟Design Pattern里的Decorator搞混了,其实这是完全不同的两个东西。虽然好像,...

基于Python解密仿射密码

基于Python解密仿射密码

新学期有一门密码学课,课上老师布置了一道密码学题,题目如下: 解密由仿射密码加密的密文“DBUHU SPANO SMPUS STMIU SBAKN OSMPU SS” 想解密这个密文,首...

如何分离django中的媒体、静态文件和网页

django项目中,占很大体积的是静态文件,媒体文件还有html代码,那我们该如何把它们分离出来以方便我们和服务器去管理和使用它们。 static 文件 static ,顾名思义就是静态...

浅谈python类属性的访问、设置和删除方法

类属性和对象属性 我们把定义在类中的属性称为类属性,该类的所有对象共享类属性,类属性具有继承性,可以为类动态地添加类属性。 对象在创建完成后还可以为它添加额外的属性,我们把这部分属性称为...

Python2与Python3的区别实例分析

本文实例讲述了Python2与Python3的区别。分享给大家供大家参考,具体如下: python2与python3的区别 1、性能 2、编码格式utf-8 3、打印语句变成了打印函数...