sqlalchemy+python链接数据库

作者: 忘了呼吸的那只猫 | 来源:发表于2018-11-06 17:12 被阅读12次
先安装 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()
sqlalchemy+python链接数据库

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读
