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>
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:/# 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:/#