sqlalchemy python数据库实战 pdf_sqlalchemy+python链接数据库

先安装 SQLAlchemy:

$ sudo apt-get update

$ sudo pip3 install sqlalchemy

安装一个 Python 与 MySQL 之间的驱动程序：

$ sudo pip3 install pymysql

测试连接：

# coding: utf-8

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root@localhost:3306/blog')

print(engine)

将 MySQL 默认的 latin1 编码改成 utf8 。

$ sudo vim /etc/mysql/my.cnf

添加下面几个配置编码改成 utf8：

[client]

default-character-set = utf8

[mysqld]

character-set-server = utf8

[mysql]

default-character-set = utf8

启动 MySQL 服务：

$ sudo service mysql start

启动 MySQL：

$ mysql -uroot -p

Faker 就是用来生成虚假数据的库。 安装它：

$ sudo pip3 install faker

一对多：

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)

username = Column(String(64), nullable=False, index=True)

password = Column(String(64), nullable=False)

email = Column(String(64), nullable=False, index=True)

articles = relationship('Article')

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.username)

class Article(Base):

__tablename__ = 'articles'

id = Column(Integer, primary_key=True)

title = Column(String(255), nullable=False, index=True)

content = Column(Text)

user_id = Column(Integer, ForeignKey('users.id'))

author = relationship('User')

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.title)

结果：

(1)

mysql> show create table articles\G;

*************************** 1. row ***************************

Table: articles

Create Table: CREATE TABLE `articles` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL,

`content` text,

`user_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `user_id` (`user_id`),

KEY `ix_articles_title` (`title`),

CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

(2)

mysql> show create table users\G;

*************************** 1. row ***************************

Table: users

Create Table: CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(64) NOT NULL,

`password` varchar(64) NOT NULL,

`email` varchar(64) NOT NULL,

PRIMARY KEY (`id`),

KEY `ix_users_username` (`username`),

KEY `ix_users_email` (`email`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

一对一：

# coding: utf-8

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, String, Integer, Text

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

engine = create_engine('mysql+pymysql://root@localhost:3306/blog?charset=utf8')

Base = declarative_base()

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)

username = Column(String(64), nullable=False, index=True)

password = Column(String(64), nullable=False)

email = Column(String(64), nullable=False, index=True)

articles = relationship('Article', backref='author')

userinfo = relationship('UserInfo', backref='user', uselist=False)

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.username)

class Article(Base):

__tablename__ = 'articles'

id = Column(Integer, primary_key=True)

title = Column(String(255), nullable=False, index=True)

content = Column(Text)

user_id = Column(Integer, ForeignKey('users.id'))

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.title)

class UserInfo(Base):

__tablename__ = 'userinfos'

id = Column(Integer, primary_key=True)

name = Column(String(64))

qq = Column(String(11))

phone = Column(String(11))

link = Column(String(64))

user_id = Column(Integer, ForeignKey('users.id'))

Base.metadata.create_all(engine)

结果：

(1)

mysql> show create table articles\G;

*************************** 1. row ***************************

Table: articles

Create Table: CREATE TABLE `articles` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL,

`content` text,

`user_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `user_id` (`user_id`),

KEY `ix_articles_title` (`title`),

CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

(2)

mysql> show create table userinfos\G;

*************************** 1. row ***************************

Table: userinfos

Create Table: CREATE TABLE `userinfos` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(64) DEFAULT NULL,

`qq` varchar(11) DEFAULT NULL,

`phone` varchar(11) DEFAULT NULL,

`link` varchar(64) DEFAULT NULL,

`user_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `user_id` (`user_id`),

CONSTRAINT `userinfos_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

(3)

ysql> show create table users\G;

*************************** 1. row ***************************

Table: users

Create Table: CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(64) NOT NULL,

`password` varchar(64) NOT NULL,

`email` varchar(64) NOT NULL,

PRIMARY KEY (`id`),

KEY `ix_users_username` (`username`),

KEY `ix_users_email` (`email`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

多对多：

# coding: utf-8

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, String, Integer, Text

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

from sqlalchemy import Table

engine = create_engine('mysql+pymysql://root@localhost:3306/blog?charset=utf8')

Base = declarative_base()

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)

username = Column(String(64), nullable=False, index=True)

password = Column(String(64), nullable=False)

email = Column(String(64), nullable=False, index=True)

articles = relationship('Article', backref='author')

userinfo = relationship('UserInfo', backref='user', uselist=False)

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.username)

class Article(Base):

__tablename__ = 'articles'

id = Column(Integer, primary_key=True)

title = Column(String(255), nullable=False, index=True)

content = Column(Text)

user_id = Column(Integer, ForeignKey('users.id'))

cate_id = Column(Integer,ForeignKey('categories.id'))

tags = relationship('Tag',secondary='article_tag',backref='articles')

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.title)

class UserInfo(Base):

__tablename__ = 'userinfos'

id = Column(Integer, primary_key=True)

name = Column(String(64))

qq = Column(String(11))

phone = Column(String(11))

link = Column(String(64))

user_id = Column(Integer, ForeignKey('users.id'))

class Category(Base):

__tablename__ = 'categories'

id = Column(Integer,primary_key=True)

name = Column(String(64),nullable=False,index=True)

articles = relationship('Article',backref='category')

def __repr__(self):

return '%s(%r)'%(self.__class__.__name__,self.name)

article_tag = Table(

'article_tag', Base.metadata,

Column('article_id', Integer, ForeignKey('articles.id')),

Column('tag_id', Integer, ForeignKey('tags.id'))

)

class Tag(Base):

__tablename__ = 'tags'

id = Column(Integer, primary_key=True)

name = Column(String(64), nullable=False, index=True)

def __repr__(self):

return '%s(%r)' % (self.__class__.__name__, self.name)

if __name__ == "__main__":

Base.metadata.create_all(engine)

结果：

(1)

mysql> show create table users\G;

*************************** 1. row ***************************

Table: users

Create Table: CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(64) NOT NULL,

`password` varchar(64) NOT NULL,

`email` varchar(64) NOT NULL,

PRIMARY KEY (`id`),

KEY `ix_users_username` (`username`),

KEY `ix_users_email` (`email`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

结果(2)

mysql> show create table userinfos\G;

*************************** 1. row ***************************

Table: userinfos

Create Table: CREATE TABLE `userinfos` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(64) DEFAULT NULL,

`qq` varchar(11) DEFAULT NULL,

`phone` varchar(11) DEFAULT NULL,

`link` varchar(64) DEFAULT NULL,

`user_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `user_id` (`user_id`),

CONSTRAINT `userinfos_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

结果(3)

mysql> show create table articles\G;

*************************** 1. row ***************************

Table: articles

Create Table: CREATE TABLE `articles` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL,

`content` text,

`user_id` int(11) DEFAULT NULL,

`cate_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `user_id` (`user_id`),

KEY `cate_id` (`cate_id`),

KEY `ix_articles_title` (`title`),

CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),

CONSTRAINT `articles_ibfk_2` FOREIGN KEY (`cate_id`) REFERENCES `categories` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

结果(4)

mysql> show create table categories\G;

*************************** 1. row ***************************

Table: categories

Create Table: CREATE TABLE `categories` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(64) NOT NULL,

PRIMARY KEY (`id`),

KEY `ix_categories_name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

结果(5)

mysql> show create table tags\G;

*************************** 1. row ***************************

Table: tags

Create Table: CREATE TABLE `tags` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(64) NOT NULL,

PRIMARY KEY (`id`),

KEY `ix_tags_name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

结果(6)

mysql> show create table tags\G;

*************************** 1. row ***************************

Table: tags

Create Table: CREATE TABLE `tags` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(64) NOT NULL,

PRIMARY KEY (`id`),

KEY `ix_tags_name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

往数据库表格插入数据：

import random

# 导入 faker 工厂对象

from faker import Factory

from sqlalchemy.orm import sessionmaker

# 创建一个 faker 工厂对象

faker = Factory.create()

Session = sessionmaker(bind=engine)

session = Session()

faker_users = [User(

# 使用 faker 生成一个人名

username=faker.name(),

# 使用 faker 生成一个单词

password=faker.word(),

# 使用 faker 生成一个邮箱

email=faker.email(),

) for i in range(10)]

# add_all 一次性添加多个对象

session.add_all(faker_users)

# 生成 5 个分类

faker_categories = [Category(name=faker.word()) for i in range(5)]

session.add_all(faker_categories)

# 生成 20 个标签

faker_tags= [Tag(name=faker.word()) for i in range(20)]

session.add_all(faker_tags)

# 生成 100 篇文章

for i in range(100):

article = Article(

# sentence() 生成一句话作为标题

title=faker.sentence(),

# 文章内容为随机生成的 10-20句话

content=' '.join(faker.sentences(nb=random.randint(10, 20))),

# 从生成的用户中随机取一个作为作者

author=random.choice(faker_users),

# 从生成的分类中随机取一个作为分类

category=random.choice(faker_categories)

)

# 从生成的标签中随机取 2-5 个作为分类，注意 sample() 函数的用法

for tag in random.sample(faker_tags, random.randint(2, 5)):

article.tags.append(tag)

session.add(article)

session.commit()