python mysql orm_python-MYSQL（包括ORM）交互

最新推荐文章于 2024-03-12 17:32:44 发布

weixin_39621044

最新推荐文章于 2024-03-12 17:32:44 发布

阅读量98

点赞数

文章标签： python mysql orm

本文链接：https://blog.csdn.net/weixin_39621044/article/details/111437364

版权

1.首先，我们必须得连上我们的MYSQL数据库。个人遇到连不上MYSQL数据的问题主要有：数据库的权限问题、数据库表权限的问题

同时获取数据库中的数据等。

//==========================================================================

数据库连不上：

授权：

grant all on *.* to 'root'@'youruser' identified by 'yourpassword';

flush privileges;

//==========================================================================

//连接数据库

//==========================================================================

1 importpymysql2 #创建连接相当于创建了一个socket

3 conn = pymysql.connect(host='localhost', port=3306, user='LOCALHOST',password='123456',db='lll')4 #创建游标

5 cursor =conn.cursor()6

7 #执行SQL，并返回收影响行数

8 effect_row = cursor.execute("select * from student") print(effect_row)9 print(cursor.fetchone())10 print(cursor.fetchone())#获取一条数据

11 print('====================================')12 print(cursor.fetchall())#获取前面没有获取到的所有数据相当于和读文件一样

2.插入数据

1 importpymysql2 #创建连接相当于创建了一个socket

3 conn = pymysql.connect(host='localhost', port=3306, user='LOCALHOST',password='123456',db='lll')4 #创建游标

5 cursor =conn.cursor()6 data =[7 ("N1","22","2018-5-25"),8 ("N2","23","2018-5-25"),9 ("N3","24","2018-5-25")10 ]11 cursor.executemany("insert into student(name,age,register_date)values(%s,%s,%s)", data)12 #提交，不然无法保存新建或者修改的数据

13 conn.commit() #默认开启事务的需要提交

14 //==========================================================================

3.查询数据

1 //==========================================================================

2 #查询数据

3 importpymysql4 #创建连接相当于创建了一个socket

5 conn = pymysql.connect(host='localhost', port=3306, user='LOCALHOST',password='123456',db='lll')6 #创建游标

7 cursor =conn.cursor()8 #获取第一行数据

9 row_1 =cursor.fetchone()10 #获取前n行数据

11 #row_2 = cursor.fetchmany(3)

12 #获取所有数据

13 #row_3 = cursor.fetchall()

14 conn.commit()15 cursor.close()16 conn.close()17 //==========================================================================

4.SQLAchemy

orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

在Python中，最有名的ORM框架是SQLAlchemy。用户包括openstack＼Dropbox等知名公司或应用，主要用户列表

创建表：

1 //==========================================================================

2 //SQLAchemy3 //==========================================================================

4 importsqlalchemy5 from sqlalchemy importcreate_engine6 from sqlalchemy.ext.declarative importdeclarative_base7 from sqlalchemy importColumn, Integer, String8

9 engine = create_engine("mysql+pymysql://LOCALHOST:123456@localhost/lll",10 encoding='utf-8', echo=True)#如果不echo = true 就不打印

12 Base = declarative_base() #生成orm基类

14 classUser(Base):15 __tablename__ = 'user' #表名

16 id = Column(Integer, primary_key=True)17 name = Column(String(32))18 password = Column(String(64))19

20 Base.metadata.create_all(engine) #创建表结构

#===========================================================================

//另一种创建方法不常用的

#===========================================================================

1 #===========================================================================

2 //另一种创建方法不常用的3 #===========================================================================

4 from sqlalchemy importTable, MetaData, Column, Integer, String, ForeignKey5 from sqlalchemy.orm importmapper6 metadata =MetaData()7

8 #创建一个表

9 user = Table('user', metadata,10 Column('id', Integer, primary_key=True),11 Column('name', String(50)),12 Column('fullname', String(50)),13 Column('password', String(12))14 )15 #一般不用

16 classUser(object):17 def __init__(self, name, fullname, password):18 self.name =name19 self.fullname =fullname20 self.password =password21

22 mapper(User, user)23 #the table metadata is created separately with the Table construct,

24 #then associated with the User class via the mapper() function

25 #===========================================================================

#===========================================================================

#最基本的表我们创建好了，那我们开始用orm创建一条数据试试

#===========================================================================

1 #===========================================================================

2 #最基本的表我们创建好了，那我们开始用orm创建一条数据试试

3 #===========================================================================

4 from sqlalchemy.orm importsessionmaker5 #最基本的表我们创建好了，那我们开始用orm创建一条数据试试

6 Session_class = sessionmaker(bind = engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

7 Session = Session_class() #生成session实例

9 user_obj = User(name="NEOKARL",password="NEOKarl") #生成你要创建的数据对象

10 user_obj2 = User(name="NEOKARL2",password="NEOKarl2") #生成你要创建的数据对象

11 print(user_obj.name,user_obj.id) #此时还没创建对象呢，不信你打印一下id发现还是None

13 Session.add(user_obj) #把要创建的数据对象添加到这个session里，一会统一创建

14 Session.add(user_obj2) #把要创建的数据对象添加到这个session里，一会统一创建

15 print(user_obj.name,user_obj.id) #此时也依然还没创建

17 Session.commit() #现此才统一提交，创建数据

# =========================================================================

# 查询数据

# =========================================================================

在查询之前修改部分代码：

1 #===========================================================================

2 #查询数据

3 #===========================================================================

4 classUser(Base):5 __tablename__ = 'user' #表名

6 id = Column(Integer, primary_key=True)7 name = Column(String(32))8 password = Column(String(64))9

10 def __repr__(self):11 return "" %(self.id,self.name)

1 #=========================================================================

2 #查询所有的数据

3 #=========================================================================

4 data =Session.query(User).filter_by().all()5 print(data)6 #查询数据

7 data = Session.query(User).filter_by(name="NEOKARL").all()8 print(data[0].name,data[0].password)9 #查询id>2的所有数据

10 data = Session.query(User).filter(User.id >2).all()11 print(data[0].name,data[0].password)12

13 #查询id=2的所有数据

14 data = Session.query(User).filter_by(id = 2).all()15 print(data[0].name,data[0].password)16

17 data = Session.query(User).filter(User.id==2).all()18 print(data[0].name,data[0].password)19

20 #多条件查询：

21 data = Session.query(User).filter(User.id>2).filter(User.id<5).all()22 print(data[0].name,data[0].password)

1 #=========================================================================

2 #修改

3 #=========================================================================

4 my_user = Session.query(User).filter_by(name="NEOKARL").first()5 my_user.name = "PerfectWanan"

6 Session.commit()7 00

8 #=========================================================================

9 #回滚

10 #=========================================================================

12 fake_user = User(name='Rain', password='12345')13 Session.add(fake_user)14

15 print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #这时看session里有你刚添加和修改的数据

17 Session.rollback() #此时你rollback一下

19 print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #再查就发现刚才添加的数据没有了。

21 #=========================================================================

22 #分组统计

23 #=========================================================================

24 print(Session.query(User).filter(User.name.in_(['Jack','rain'])).count())25 #from sqlalchemy import func

26 #print(Session.query(func.count(User.name),User.name).group_by(User.name).all() )

27 #=========================================================================

28 #链表

29 #=========================================================================

30 s1 = Student(name = "3333",age = "55",register_date = "2018-5-25")31 Session.add(s1)32 Session.commit()33 #=========================================================================

34 #join连表查询

35 #=========================================================================

36 print(Session.query(User,Student).filter(User.id ==Student.stu_id).all())37 #需要有外部关联才能查询

38 #print(Session.query(User).join(Student).all())

外键关联：

1 importsqlalchemy2 from sqlalchemy importcreate_engine3 from sqlalchemy.ext.declarative importdeclarative_base4 from sqlalchemy importColumn, Integer, String5 #需要导入其他的模块

6 from sqlalchemy.orm importsessionmaker7 #我们创建一个表，跟student表关联

8 from sqlalchemy importForeignKey9 from sqlalchemy.orm importrelationship10 #创建一个表

11 #如果已经创建了就不再需要创建了

12 engine = create_engine("mysql+pymysql://LOCALHOST:123456@localhost/lll",13 encoding='utf-8')#如果不echo = true 就不打印

15 Base = declarative_base() #生成orm基类

17 classStudent(Base):18 __tablename__ = 'student'

19 stu_id = Column(Integer, primary_key=True)20 name = Column(String(32), nullable=False)21 age = Column(String(32), nullable=False)22 register_date = Column(String(32), nullable=False)23

24 #user = relationship("User", backref="addresses") #这个nb，允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项

26 def __repr__(self):27 return "" %(self.stu_id,self.name)28

29 classStudyRecord(Base):30 __tablename__ = 'study_record'

31 id = Column(Integer, primary_key=True)32 day = Column(Integer,nullable=False)33 status = Column(String(32),nullable=False)34

35 #===========================================================

36 student = relationship("Student", backref="my_study_record")37

38 #student = query(Student).filter(Student.id == stu_obj.stu_id).first()

39 #===========================================================

40 #外键关联

41 stu_id = Column(Integer,ForeignKey("student.stu_id"))42

43 def __repr__(self):44 return "" %(self.student.name,self.day,self.status)45

46 Base.metadata.create_all(engine) #创建表结构

48 #最基本的表我们创建好了，那我们开始用orm创建一条数据试试

49 Session_class = sessionmaker(bind = engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

50 Session = Session_class() #生成session实例

52 #s1 = Student(name = "jack",age = "5551",register_date = "2018-5-25")

53 #s2 = Student(name = "neo",age = "5552",register_date = "2018-5-26")

54 #s3 = Student(name = "karl",age = "5553",register_date = "2018-5-27")

55 #s4 = Student(name = "perfectwanan",age = "5554",register_date = "2018-5-28")

57 #study_obj1 = StudyRecord(day = 1,status = "YES",stu_id = 3)

58 #study_obj2 = StudyRecord(day = 2,status = "NO",stu_id = 3)

59 #study_obj3 = StudyRecord(day = 3,status = "YES",stu_id = 3)

60 #study_obj4 = StudyRecord(day = 3,status = "YES",stu_id = 4)

62 #Session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4])

64 study_obj = Session.query(Student).filter(Student.name == "neo").first()65 print(study_obj.my_study_record)66

68 Session.commit()

#===========================================================================

#结果:[, , ]

#===========================================================================

需要注意的是：

我的数据表为：

#===========================================================================

#sqlalchemy 多外键关联

#===========================================================================

orm_many_fk.py

1 importsqlalchemy2 from sqlalchemy importcreate_engine3 from sqlalchemy.ext.declarative importdeclarative_base4 from sqlalchemy importColumn, Integer, String5 #需要导入其他的模块

6 from sqlalchemy.orm importsessionmaker7 #我们创建一个表，跟student表关联

8 from sqlalchemy importForeignKey9 from sqlalchemy.orm importrelationship10 engine = create_engine("mysql+pymysql://LOCALHOST:123456@localhost/lll",11 encoding='utf-8',echo = True)#如果不echo = true 就不打印

12 Base =declarative_base()13

14 classCustomer(Base):15 __tablename__ = 'customer'

16 id = Column(Integer, primary_key=True)17 name = Column(String(64))18

19 billing_address_id = Column(Integer, ForeignKey("address.id"))20 shipping_address_id = Column(Integer, ForeignKey("address.id"))21

22 billing_address = relationship("Address", foreign_keys=[billing_address_id])23 shipping_address = relationship("Address", foreign_keys=[shipping_address_id])24

25 classAddress(Base):26 __tablename__ = 'address'

27 id = Column(Integer, primary_key=True)28 street = Column(String(64))29 city = Column(String(64))30 state = Column(String(64))31 Base.metadata.create_all(engine) #创建表结构

orm_api.py

1 importorm_many_fk2 #需要导入其他的模块

3 from sqlalchemy.orm importsessionmaker4 #最基本的表我们创建好了，那我们开始用orm创建一条数据试试

5 Session_class = sessionmaker(bind = orm_many_fk.engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

6 Session = Session_class() #生成session实例

8 addr1 = orm_many_fk.Address(street = "Tiantongyuan",city = "Changping",state = "BJ")9 addr2 = orm_many_fk.Address(street = "Wudaokou",city = "Haidian",state = "BJ")10 addr3 = orm_many_fk.Address(street = "Yanjiao",city = "Langfang",state = "HB")11

12 Session.add_all([addr1,addr2,addr3])13

14 c1 = orm_many_fk.Customer(name = "wanan",billing_address= addr1,shipping_address =addr2)15 c2 = orm_many_fk.Customer(name = "jcak",billing_address= addr3,shipping_address =addr2)16

17 Session.add_all([c1,c2])

#===========================================================================

#如果出现以下问题提示

#===========================================================================

sqlalchemy.exc.AmbiguousForeignKeysError: Couldnot determine joincondition between parent/child tables on relationshipCustomer.billing_address- there are multiple foreign keypaths linking the tables. Specify the'foreign_keys' argument,providing alist of those columns which should becounted as containing a foreign key reference to the parent table.

#===========================================================================

1 classCustomer(Base):2 __tablename__ = 'customer'

3 id = Column(Integer, primary_key=True)4 name = Column(String(64))5

6 billing_address_id = Column(Integer, ForeignKey("address.id"))7 shipping_address_id = Column(Integer, ForeignKey("address.id"))8

9 billing_address = relationship("Address", foreign_keys=[billing_address_id])10 shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

这样sqlachemy就能分清哪个外键是对应哪个字段了

#===========================================================================

#sqlalchemy 多对多外键关联

#===========================================================================

1 #一本书可以有多个作者，一个作者又可以出版多本书

2 from sqlalchemy importTable, Column, Integer,String,DATE, ForeignKey3 from sqlalchemy.orm importrelationship4 from sqlalchemy.ext.declarative importdeclarative_base5 from sqlalchemy importcreate_engine6 from sqlalchemy.orm importsessionmaker7 Base =declarative_base()8

9 book_m2m_author = Table('book_m2m_author', Base.metadata,10 Column('book_id',Integer,ForeignKey('books.id')),11 Column('author_id',Integer,ForeignKey('authors.id')),12 )13

14 classBook(Base):15 __tablename__ = 'books'

16 id = Column(Integer,primary_key=True)17 name = Column(String(64))18 pub_date =Column(DATE)19 authors = relationship('Author',secondary=book_m2m_author,backref='books')20

21 def __repr__(self):22 returnself.name23

24 classAuthor(Base):25 __tablename__ = 'authors'

26 id = Column(Integer, primary_key=True)27 name = Column(String(32))28

29 def __repr__(self):30 returnself.name31 #创建一个表

32 #如果已经创建了就不再需要创建了

33 engine = create_engine("mysql+pymysql://LOCALHOST:123456@localhost/lll",34 encoding='utf-8')#如果不echo = true 就不打印

1 importorm_mtom2 from sqlalchemy.orm importsessionmaker3 #最基本的表我们创建好了，那我们开始用orm创建一条数据试试

4 Session_class = sessionmaker(bind = orm_mtom.engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

5 s = Session_class() #生成session实例

7 #b1 = Book(name="跟0学Python")

8 #b2 = Book(name="跟0学把妹")

9 #b3 = Book(name="跟0学装逼")

10 #b4 = Book(name="跟0学开车")

12 #a1 = Author(name="Alex")

13 #a2 = Author(name="Jack")

14 #a3 = Author(name="Rain")

16 #b1.authors = [a1,a2]

17 #b2.authors = [a1,a2,a3]

19 #s.add_all([b1,b2,b3,b4,a1,a2,a3])

21 print('--------通过书表查关联的作者---------')22

23 book_obj = s.query(Book).filter_by(name="跟0学Python").first()24 print(book_obj.name, book_obj.authors)25 print('--------通过作者表查关联的书---------')26 author_obj =s.query(Author).filter_by(name="0").first()27 print(author_obj.name , author_obj.books)28

29 s.commit()

weixin_39621044

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
python mysql orm_python-MYSQL（包括ORM）交互

1.首先，我们必须得连上我们的MYSQL数据库。个人遇到连不上MYSQL数据的问题主要有：数据库的权限问题、数据库表权限的问题同时获取数据库中的数据等。//==========================================================================数据库连不上：授权：grant all on *.* to 'root'@'youruser' ...
复制链接

扫一扫

python mysql orm_python-MYSQL（包括ORM）交互

“相关推荐”对你有帮助么？