sqlalchemy

  • 建立鏈接并創(chuàng)建一張表
# 環(huán)境python2.7
# 已經(jīng)創(chuàng)建了blog數(shù)據(jù)庫(kù)
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer

#創(chuàng)建引擎
engine = create_engine('mysql+mysqldb://root@localhost:3306/blog?password=admin?charset=utf8')
Base = declarative_base()

# print(engine)
class User(Base):#
    __tablename__= 'users'#指定創(chuàng)建的表的名稱(chēng)
    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)

Base.metadata.create_all(engine)
# 除了String 、Integer還有Text 、Boolean、 SmallInteger、 DateTime
圖片.png

創(chuàng)建一對(duì)多關(guān)系

  • 雙方使用relationship
# 創(chuàng)建一對(duì)多的關(guān)系，即一個(gè)人對(duì)應(yīng)多個(gè)博客文章
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Text
from sqlalchemy.orm import relationship, backref
#創(chuàng)建引擎
engine = create_engine('mysql+mysqldb://root@localhost:3306/blog?password=admin?charset=utf8')
Base = declarative_base()

# print(engine)
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')

class Article(Base):
    __tablename__ = 'articles'
    id = Column(Integer, primary_key = True)
    title = Column(String(64), nullable = False, index = True)
    content = Column(Text)
    user_id = Column(Integer, ForeignKey('users.id'))
    author = relationship('users')
        
Base.metadata.create_all(engine)
  • 使用backref 只需用在user中表明關(guān)系即可，ariticles中就不用在表明關(guān)系了
# coding:utf8
# 環(huán)境python2.7
# 已經(jīng)創(chuàng)建了blog數(shù)據(jù)庫(kù)
# 創(chuàng)建一對(duì)多的關(guān)系，即一個(gè)人對(duì)應(yīng)多個(gè)博客文章
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Text
from sqlalchemy.orm import relationship, backref
#創(chuàng)建引擎
engine = create_engine('mysql+mysqldb://root@localhost:3306/blog?password=admin?charset=utf8')
Base = declarative_base()

# print(engine)
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')
    articles = relationship('Article', backref = 'author')

class Article(Base):
    __tablename__ = 'articles'
    id = Column(Integer, primary_key = True)
    title = Column(String(64), nullable = False, index = True)
    content = Column(Text)
    user_id = Column(Integer, ForeignKey('users.id'))
    # author = relationship('users')
        
Base.metadata.create_all(engine)

一對(duì)一關(guān)系

  • userinfo 存著用戶(hù)無(wú)關(guān)緊要的一些數(shù)據(jù)，一對(duì)一是基于一對(duì)多的，只增加uselist = False
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Text
from sqlalchemy.orm import relationship, backref
#創(chuàng)建引擎
engine = create_engine('mysql+mysqldb://root@localhost:3306/blog?password=admin?charset=utf8')
Base = declarative_base()

# print(engine)
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')
    articles = relationship('Article', backref = 'author')
    userinfo = relationship('UserInfo', backref = 'user', uselist = False) #uselist指明為一對(duì)一關(guān)系。

class Article(Base):

    __tablename__ = 'articles'

    id = Column(Integer, primary_key = True)
    title = Column(String(64), nullable = False, index = True)
    content = Column(Text)
    user_id = Column(Integer, ForeignKey('users.id'))
    # author = relationship('users')

class UserInfo(Base):

    __tablename__ = 'userinfo'

    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)

多對(duì)多關(guān)系

  • 多對(duì)多需要sqlalchemy的Table類(lèi)進(jìn)行支持，如下是創(chuàng)建了標(biāo)簽與文章之間的多對(duì)多關(guān)系表
from sqlalchemy import create_engine, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Text
from sqlalchemy.orm import relationship, backref
#創(chuàng)建引擎
engine = create_engine('mysql+mysqldb://root@localhost:3306/blog?password=admin?charset=utf8')
Base = declarative_base()

# print(engine)
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')
    articles = relationship('Article', backref = 'author')
    userinfo = relationship('UserInfo', backref = 'user', uselist = False) #uselist指明為一對(duì)一關(guān)系。

class Article(Base):

    __tablename__ = 'articles'

    id = Column(Integer, primary_key = True)
    title = Column(String(64), nullable = False, index = True)
    content = Column(Text)
    user_id = Column(Integer, ForeignKey('users.id'))
tags = relationship('Tag', secondary='article_tag', backref='articles')
    # author = relationship('users')

class UserInfo(Base):

    __tablename__ = 'userinfo'

    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'))

articles_tag = Table(
    # 使用此類(lèi)必須包含表名稱(chēng)及metadata,
    'article_tag', Base.metadata,
    Column('article_id', Integer, ForeignKey('articles.id')),
    Column('tag_id', Integer, ForeignKey('tags.id'))
    )#通過(guò)此類(lèi)將article_id與tag_id關(guān)聯(lián)成多對(duì)多關(guān)系

class Tag(Base):
    
    __tablename__ = 'tags'

    id = Column(Integer, primary_key = True)
    name = Column(String(64), nullable = False, index = True)
        
Base.metadata.create_all(engine)

通過(guò)與mysql數(shù)據(jù)庫(kù)建立會(huì)話(huà)將數(shù)據(jù)存入數(shù)據(jù)庫(kù)

# coding:utf8
# 環(huán)境python2.7
# 已經(jīng)創(chuàng)建了blog數(shù)據(jù)庫(kù)
# 創(chuàng)建一對(duì)多的關(guān)系，即一個(gè)人對(duì)應(yīng)多個(gè)博客文章
from sqlalchemy import create_engine, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Text
from sqlalchemy.orm import relationship, backref, sessionmaker
from faker import Factory
import random
#創(chuàng)建引擎
engine = create_engine('mysql+mysqldb://root@localhost:3306/blog?password=admin?charset=utf8')
Base = declarative_base()

# print(engine)
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')
    articles = relationship('Article', backref = 'author')
    userinfo = relationship('UserInfo', backref = 'user', uselist = False) #uselist指明為一對(duì)一關(guān)系。

class Article(Base):

    __tablename__ = 'articles'

    id = Column(Integer, primary_key = True)
    title = Column(String(64), nullable = False, index = True)
    content = Column(Text)
    user_id = Column(Integer, ForeignKey('users.id'))
    tag = relationship('Tag', secondary='article_tag', backref='articles')
    # author = relationship('users')

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 Tag(Base):
    
    __tablename__ = 'tags'

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

articles_tag = Table(
    # 使用此類(lèi)必須包含表名稱(chēng)及metadata,
    'article_tag', Base.metadata,
    Column('article_id', Integer, ForeignKey('articles.id')),
    Column('tag_id', Integer, ForeignKey('tags.id'))
    )#通過(guò)此類(lèi)將article_id與tag_id關(guān)聯(lián)成多對(duì)多關(guān)系

if __name__ == '__main__':
            
    Base.metadata.create_all(engine)

    # 創(chuàng)建工廠對(duì)象
    faker = Factory.create()
    # 創(chuàng)建與mysql數(shù)據(jù)庫(kù)進(jìn)會(huì)話(huà)的鏈接
    Session = sessionmaker(bind = engine)
    # 創(chuàng)建鏈接實(shí)例
    session = Session()

    # 使用faker生成假數(shù)據(jù)
    # 生成用戶(hù)假數(shù)據(jù)
    faker_users = [User(username = faker.name(), password = faker.word(), email = faker.email()) for i in range(10)]
     # 將生成的10條假數(shù)據(jù)添加到數(shù)據(jù)庫(kù)
    session.add_all(faker_users)

    # 生成標(biāo)簽假數(shù)據(jù)
    faker_tags = [Tag(name = faker.name()) for i in range(5)]
    session.add_all(faker_tags)

    # 生成100篇文章
    for i in range(100):
        article = Article(
            title = faker.word(),
            content = ' '.join(faker.sentences(nb = random.randint(10,20))),#隨機(jī)生成幾句話(huà)
            author = random.choice(faker_users),
            )
        for tag in random.sample(faker_tags, random.randint(2,5)):#隨機(jī)選取2到5個(gè)
            article.tag.append(tag)
        session.add(article)# 添加單個(gè)數(shù)據(jù)到數(shù)據(jù)庫(kù)

    session.commit()# 提交任務(wù)
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容