1、驱动安装
1 2 |
pip install SQLAlchemy pip install pymsql |
2、连接数据库
1 2 |
'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名' mysql+pymysql://quanjie:mima@web1:3306/sqlalchemy?charset=utf8 |
3、具体操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 |
#Connecting from sqlalchemy import create_engine #echo=true输出所有生成的sql,该功能通过logging模块实现 ''' The return value of create_engine() is an instance of Engine, and it represents the core interface to the database, adapted through a dialect that handles the details of the database and DBAPI in use. The first time a method like Engine.execute() or Engine.connect() is called, the Engine establishes a real DBAPI connection to the database, which is then used to emit the SQL. ''' engine = create_engine('mysql+pymysql://quanjie:mima@web1:3306/sqlalchemy?charset=utf8',echo = True) #Declare a Mapping ''' Classes mapped using the Declarative system are defined in terms of a base class which maintains a catalog of classes and tables relative to that base - this is known as the declarative base class. Our application will usually have just one instance of this base in a commonly imported module. We create the base class using the declarative_base() function, as follows: ''' from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() from sqlalchemy import Column, Integer, String class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(20)) fullname = Column(String(20)) password = Column(String(20)) def __repr__(self): return "<User(name='%s', fullname='%s', password='%s')>" % (self.name, self.fullname, self.password) print(User.__table__) #Create a Schema #查看User表的metadata信息 User.__table__ #调用base的metadata.create_all方法在数据库中创建没有的表 Base.metadata.create_all(engine) #Create an Instance of the Mapped Class ed_user = User(name='ed', fullname='Ed Jones', password='edspassword') """ >> ed_user.name 'ed' >> ed_user.password 'edspassword' >> str(ed_user.id) 'None' """ #Creating a Session from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind=engine) ''' #另一种创建Session的方法 Session = sessionmaker() Session.configure(bind=engine) # once engine is available ''' #实例华Session session = Session() #Adding and Updating Objects ed_user = User(name='ed', fullname='Ed Jones', password='edspassword') session.add(ed_user) session.add_all([ User(name='wendy', fullname='Wendy Williams', password='foobar'), User(name='mary', fullname='Mary Contrary', password='xxg527'), User(name='fred', fullname='Fred Flinstone', password='blah')]) ed_user.password = 'f8s7ccs' session.dirty session.new #所用的操作都要commit后才会在数据库生效 session.commit() """ >> ed_user.id 1 """ #Building a Relationship from sqlalchemy import ForeignKey from sqlalchemy.orm import relationship class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email_address = Column(String(20), nullable=False) user_id = Column(Integer, ForeignKey('users.id')) user = relationship("User", back_populates="addresses") def __repr__(self): return "<Address(email_address='%s')>" % self.email_address User.addresses = relationship( "Address", order_by=Address.id, back_populates="user") Base.metadata.create_all(engine) #Working with Related Objects >> jack = User(name='jack', fullname='Jack Bean', password='gjffdd') >> jack.addresses [] jack.addresses = [ Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')] session.add(jack) session.commit() #Querying with Joins for u, a in session.query(User, Address).\ filter(User.id==Address.user_id).\ filter(Address.email_address=='jack@google.com').\ all(): print(u) print(a) ''' SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id FROM users, addresses WHERE users.id = addresses.user_id AND addresses.email_address = ? ('jack@google.com',) ''' for u, a in session.query(User, Address).\ filter(User.id==Address.user_id).\ all(): print(u.addresses) print(a.user) jack = session.query(User).\ filter_by(name='jack').one() |
5、问题总结
(1)、编码问题
问题描述:sqlalchemy UnicodeEncodeError: 'latin-1' codec can't encode characters in position 0-3: ordinal not in range(256)
解决办法:连接数据库是如下连接:
1 |
engine = create_engine('mysql://username:password@host/database?charset=utf8',encoding='utf8',echo=False) |
- 我的微信
- 这是我的微信扫一扫
-
- 我的微信公众号
- 我的微信公众号扫一扫
-