学习sqlalchemy对mysql数据库的操作

最新推荐文章于 2024-06-08 17:50:13 发布

ljyfree

最新推荐文章于 2024-06-08 17:50:13 发布

阅读量397

点赞数 1

分类专栏： Linux 日常办公文章标签： mysql python

本文链接：https://blog.csdn.net/ljyfree/article/details/128852845

版权

Linux 同时被 2 个专栏收录

25 篇文章 1 订阅

订阅专栏

日常办公

10 篇文章 0 订阅

订阅专栏

准备mysql的docker

拉取docker镜像

admin@server:~$ docker pull mysql:8.0.32-debian

运行,后续操作都是在docker中进行的了

admin@server:~$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=mysql_admin -d mysql:8.0.32-debian
cc07ba4e55ec79e088987ee95de0b1820be0a5f7a17a160bc6668d503c41e892
admin@server:~$ 
admin@server:~$ docker exec -it some-mysql bash
root@cc07ba4e55ec:/# cat /etc/issue
Debian GNU/Linux 11 \n \l

root@cc07ba4e55ec:/#

登录mysql，用户名是root，密码是创建docker时候写入的mysql_admin

root@cc07ba4e55ec:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

创建food这个database

mysql> create DATABASE food;
Query OK, 1 row affected (0.01 sec)

mysql> use food;
Database changed
mysql>

创建fruit这个table,指定有两列，分别为name和number,就是水果名称和数量

mysql> CREATE TABLE  fruit
    -> (
    -> name VARCHAR(25),
    -> number INT(11)
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> 

mysql> show tables;
+----------------+
| Tables_in_food |
+----------------+
| fruit          |
+----------------+
1 row in set (0.01 sec)

mysql>

尝试添加两条记录

mysql> INSERT INTO fruit
    -> (name,number)
    -> VALUES
    -> ("apple",50);
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> 
mysql>  INSERT INTO fruit
    ->  (name,number)
    -> VALUES
    -> ("pear",60);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> select * from fruit;
+-------+--------+
| name  | number |
+-------+--------+
| apple |     50 |
| pear  |     60 |
+-------+--------+
2 rows in set (0.00 sec)

mysql>

准备sqlalchemy

apt更换阿里源

cat >/etc/apt/sources.list <<EOF
deb http://mirrors.aliyun.com/debian/ bullseye main non-free contrib
deb-src http://mirrors.aliyun.com/debian/ bullseye main non-free contrib
deb http://mirrors.aliyun.com/debian-security/ bullseye-security main
deb-src http://mirrors.aliyun.com/debian-security/ bullseye-security main
deb http://mirrors.aliyun.com/debian/ bullseye-updates main non-free contrib
deb-src http://mirrors.aliyun.com/debian/ bullseye-updates main non-free contrib
deb http://mirrors.aliyun.com/debian/ bullseye-backports main non-free contrib
deb-src http://mirrors.aliyun.com/debian/ bullseye-backports main non-free contrib
EOF

更新软件库,安装必要软件

root@cc07ba4e55ec:/# apt update -y
root@cc07ba4e55ec:/# apt install vim python3 python3-pip -y

pypi更换阿里源，添加文件

root@cc07ba4e55ec:/# cat ~/.config/pip/pip.conf
[global]
index-url = https://mirrors.aliyun.com/pypi/simple

[install]
trusted-host = mirrors.aliyun.com
root@cc07ba4e55ec:/#

安装需要的pypi包

root@cc07ba4e55ec:/# pip3 install cryptography sqlalchemy pymysql

调试sqlalchemy脚本

想要通过sqlalchemy
- 向table插入一些数据
- 按需查询并打印table的数据
直接上示例代码

root@cc07ba4e55ec:/# cat test_orm.py 
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Integer, String, DateTime, Column
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class FruitTable(Base):
    __tablename__ = 'fruit'
    name = Column(String(25), nullable=False, primary_key=True) 
    number = Column(Integer, nullable=False, comment="fruit number")

    def getSelf(self):
        return "Get {} {} here!".format(self.number,self.name)


engine = create_engine('mysql+pymysql://', connect_args={'user': 'root',
                                                     'password': 'mysql_admin',
                                                     'host': '127.0.0.1',
                                                     'port': 3306,
                                                     'database': 'food',
                                                     'charset': 'utf8'})
DBSession = sessionmaker(bind=engine)
session = DBSession()

def show_table():
    print('~~~print the first record~~~~~')
    fruit_info = session.query(FruitTable).first()
    print(fruit_info.name,fruit_info.number)
    
    print('~~~print all of the records~~~~~')
    all_fruit = session.query(FruitTable).order_by(FruitTable.name).all()
    for x in all_fruit:
        print(x.name,x.number)
    
    print('~~~print fruit whose number is greater than 55~~~')
    fruit_info = session.query(FruitTable).filter(FruitTable.number>55)
    for x in fruit_info:
        print(x.name,x.number)

def insert_data():
    new_fruit1 = FruitTable(name='orange',number=44)
    new_fruit2 = FruitTable(name='banana',number=77)
    new_fruit3 = FruitTable(name='mango',number=33)
    #session.add(new_user1)
    session.add_all([new_fruit1,new_fruit2,new_fruit3])
    session.commit()

insert_data()
show_table()


session.close()

root@cc07ba4e55ec:/#

运行脚本

root@cc07ba4e55ec:/# python3 test_orm.py 
~~~print the first record~~~~~
apple 50
~~~print all of the records~~~~~
apple 50
banana 77
mango 33
orange 44
pear 60
~~~print fruit whose number is greater than 55~~~
pear 60
banana 77
root@cc07ba4e55ec:/#