SQLAlchemy操作mysql使用记录

2018年4月1日23:09:41 发表评论 492 views

1、驱动安装

pip install SQLAlchemy
pip install pymsql

1 2	pip install SQLAlchemy pip install pymsql

2、连接数据库

'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
mysql+pymysql://quanjie:mima@web1:3306/sqlalchemy?charset=utf8

1 2	'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名' mysql+pymysql://quanjie:mima@web1:3306/sqlalchemy?charset=utf8

3、具体操作

#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()

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

"""

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

解决办法：连接数据库是如下连接：

engine = create_engine('mysql://username:password@host/database?charset=utf8',encoding='utf8',echo=False)

1	engine = create_engine('mysql://username:password@host/database?charset=utf8',encoding='utf8',echo=False)

我的微信
这是我的微信扫一扫

我的微信公众号
我的微信公众号扫一扫

SQLAlchemy操作mysql使用记录

1、驱动安装

2、连接数据库

3、具体操作

5、问题总结

发表评论取消回复

微信

在线咨询

1、驱动安装

2、连接数据库

3、具体操作

5、问题总结

发表评论取消回复

登录

微信

在线咨询