Python ORM - pymysql&sqlalchemy

Ten hair 2021-01-21 06:45:16
python orm pymysql&sqlalchemy pymysql sqlalchemy


Python3 Mainly object-oriented coding style , Access to the database can also use ORM Framework to achieve object-oriented , In this paper, pymysql and sqlalchemy

Installation of components

pip3 install pymysql
pip3 install sqlalchemy

Define data objects

  • user Table structure
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`age` int DEFAULT NULL,
`deleted` int DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`),
KEY `idx_age` (`age`),
KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • User object
# coding=utf-8
from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
# Create the base class of the object :
Base = declarative_base()
# Definition User object :
class User(Base):
# Name of table :
__tablename__ = 'user'
# The structure of the table :
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)
deleted = Column(Integer)
def __str__(self):
return "id: {}, name: {}, age: {}, deleted: {}" \
.format(self.id, self.name, self.age, self.deleted)

operation

Create data

# Create record
tenmao = User(name = 'tenmao', age = 100)
session.add(tenmao)
session.commit()

Update data


Query data

  • Query a piece of data
# coding=utf-8
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from model.user import User
# Database configuration
db_user = 'maibao'
db_password = 'maibao123'
db_host_port = '9.134.77.51:3306'
# Initialize database connection :
engine = create_engine(
'mysql+pymysql://{db_user}:{db_password}@{db_host_port}/tenmao'.format(db_user=db_user,
db_password=db_password,
db_host_port=db_host_port))
# establish DBSession type :
DBSession = sessionmaker(bind=engine)
# establish Session:
session = DBSession()
# establish Query Inquire about ,filter yes where Conditions , Last call one() Back to the only line , If the all() Then return to all lines :
user = session.query(User).filter(User.id==1).one()
# Print type and object of name attribute :
print(user)
# close Session:
session.close()
  • Query all the data
# Query all User
users = session.query(User).all()
print("there are {} users".format(len(users)))
  • filter
# Conditions of the query
users = session.query(User)\
.filter(User.name == 'timxia')\
.filter(User.age < 10)\
.filter(User.name.like('%tim%'))\
.order_by(User.name.desc(), User.id.asc())\
.all()
print("there are {} users for given condition".format(len(users)))

common problem

  • sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped entity expected - got '<class 'model.user.User'>' Entity table needs inheritance declarative_base()
Base = declarative_base()
class User(Base):
  • Creation or modification is not in effect

Because no submission , Need to call commit Method session.commit()

Reference resources

Participation of this paper Tencent cloud media sharing plan , You are welcome to join us , share .

版权声明
本文为[Ten hair]所创,转载请带上原文链接,感谢
https://pythonmana.com/2021/01/20210121064236946r.html

  1. 【七天搞定Python】day01.Python环境配置、pip、IDE、注释、变量,数据类型、标识符/关键字、输出、输入
  2. Life is short, I learn Python
  3. Python image enhancement and special effects - using Baidu AI to color black and white images
  4. Python environment configuration, Pip, IDE, comment, variable, data type, identifier / keyword, output, input
  5. 为什么说Python是最伟大的语言?看图就知道了 - 知乎
  6. Why is Python the greatest language? Just look at the picture. - Zhihu
  7. 通过创建视频游戏来学习 Python
  8. Learn Python by creating video games
  9. Python3版本下创建计算给定日期范围内工作日方法
  10. Creating a method to calculate working days within a given date range in Python 3
  11. 图解爬虫,用几个最简单的例子带你入门Python爬虫
  12. Graphical crawler, with a few of the simplest examples to take you to the introduction of Python crawler
  13. python+requests基础知识
  14. Basic knowledge of Python + requests
  15. python自定义windowsr日志支持文件分割
  16. python+requests基础知识
  17. Python custom Windowsr log supports file segmentation
  18. Basic knowledge of Python + requests
  19. 高级测试 | Python笔试题
  20. 火了!开源的 Python 抢票神器,过年回家就看这一波了!
  21. Python 爬虫进阶 - 前后端分离有什么了不起,过程超详细!
  22. 【python】使用pip提示ModuleNotFoundError
  23. 【python】虚拟环境搭建
  24. Advanced test | Python written test questions
  25. Fire! Open source Python ticket grabbing artifact, come home to see this wave of New Year!
  26. Python crawler advanced - before and after the end of the separation of what great, super detailed process!
  27. [Python] prompt modulenotfounderror with PIP
  28. Building a virtual environment
  29. Serverless 架构下用 Python 轻松搞定图像分类和预测
  30. Easy image classification and prediction with Python under serverless architecture
  31. python协程爬取某网站的老赖数据
  32. Python coroutine crawls Laolai data of a website
  33. 使用Python分析姿态估计数据集COCO的教程
  34. Using Python to analyze the data set coco of attitude estimation
  35. win环境 python3 flask 上手整理 环境搭建(一)
  36. Getting started with win environment python3 flash
  37. Python实现一个论文下载器,赶紧收藏
  38. win环境 python3 flask 上手整理 快速上手-基础操作(二)
  39. Python 中常见的配置文件写法
  40. Python to achieve a paper Downloader, quickly collect
  41. Python批量 png转ico
  42. 使用line_profiler对python代码性能进行评估优化
  43. 使用line_profiler对python代码性能进行评估优化
  44. Getting started with Python 3 flash in win environment
  45. Common ways to write configuration files in Python
  46. Python会在2021年死去吗? Python 3.9最终版本的回顾
  47. Python batch PNG to ICO
  48. Using line_ Profiler evaluates and optimizes the performance of Python code
  49. Using line_ Profiler evaluates and optimizes the performance of Python code
  50. Will Python die in 2021? A review of the final version of Python 3.9
  51. Python3 SMTP send mail
  52. Understanding closures in Python: getting started with closures
  53. Python日志实践
  54. Python logging practice
  55. [python opencv 计算机视觉零基础到实战] 十、图片效果毛玻璃
  56. [python opencv 计算机视觉零基础到实战] 九、模糊
  57. 10. Picture effect ground glass
  58. [Python opencv computer vision zero basis to actual combat] 9. Fuzzy
  59. 使用line_profiler對python程式碼效能進行評估優化
  60. Using line_ Profiler to evaluate and optimize the performance of Python code