CREATE TABLE `alg_alarm_push` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`device_ip` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '摄像头ip' COLLATE 'utf8_general_ci',
`ip_to_int` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'ip 地址转int',
`ori_img_url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '原始图片url' COLLATE 'utf8_general_ci',
`handle_img_url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '处理后图片url' COLLATE 'utf8_general_ci',
`detect_info` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '标注框坐标信息' COLLATE 'utf8_general_ci',
`status` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '标记(0-处理过,1-未处理)',
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`, `ip_to_int`) USING BTREE,
INDEX `device_ip` (`device_ip`) USING BTREE,
INDEX `create_time` (`create_time`) USING BTREE
)
COMMENT='算法推送积累表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
/*!50100 PARTITION BY HASH (ip_to_int)
PARTITIONS 16 */;
from sqlalchemy import create_engine, Column, Integer, String, SmallInteger, text, TIMESTAMP
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 准备连接数据库基本信息
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = '7yue'
USERNAME = 'root'
PASSWORD = '123456'
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,
password=PASSWORD,
host=HOSTNAME,
port=PORT,
db=DATABASE)
print(DB_URI)
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()
class AlgAlarmPush(Base):
__tablename__ = 'alg_alarm_push'
id = Column(Integer, primary_key=True, autoincrement=True)
device_ip = Column(String(255), primary_key=True, nullable=False, default='')
ip_to_int = Column(Integer(), nullable=False, default=0)
ori_img_url = Column(String(255), nullable=False, default='')
handle_img_url = Column(String(255), nullable=False, default='')
detect_info = Column(String(512), nullable=False, default='')
status = Column(SmallInteger, nullable=False, default=1)
create_time = Column(TIMESTAMP, nullable=False, default=text('CURRENT_TIMESTAMP'))
update_time = Column(TIMESTAMP, nullable=False, default=text('CURRENT_TIMESTAMP'),
server_onupdate=text('CURRENT_TIMESTAMP'))
def create_one():
alarm = AlgAlarmPush()
alarm.device_ip = '1'
alarm.ip_to_int = 233
alarm.ori_img_url = 'ori'
alarm.handle_img_url = 'url'
alarm.detect_info = 'detect_info'
session.add(alarm)
session.commit()
print(alarm.id)
def get_all():
res = session.query(AlgAlarmPush).all()
return res
if __name__ == '__main__':
# alarm = AlgAlarmPush()
# alarm.device_ip = '1'
# alarm.ip_to_int = 233
# alarm.ori_img_url = 'ori'
# alarm.handle_img_url = 'url'
# alarm.detect_info = 'detect_info'
# session.add(alarm)
# session.commit()
# print(alarm.id)
get_all()
a = session.query(AlgAlarmPush).first()
a.detect_info
'detect_info'
a.detect_info = 'nbbbb'
session.commit()
# 修改后 update time 会跟着修改
SQLAlchemy对数据的增删改查操作
用session做数据的增删改查操作:
1. 构建session对象:所有和数据库的ORM操作都必须通过一个叫做`session`的会话对象来实现,通过以下代码来获取会话对象:
from sqlalchemy.orm import sessionmaker
engine = create_engine(DB_URI)
Base = declarative_base(engine)
session = sessionmaker(engine)()
2. 添加对象:
* 创建对象,也即创建一条数据:
p1 = Person(name='momo1',age=19,country='china')
* 将这个对象添加到`session`会话对象中:
session.add(p1)
* 将session中的对象做commit操作(提交):
session.commit()
* 一次性添加多条数据:
p1 = Person(name='momo1',age=19,country='china')
p2 = Person(name='momo2',age=20,country='china')
session.add_all([p1,p2])
session.commit()
3. 查找对象:
# 查找某个模型对应的那个表中所有的数据:
all_person = session.query(Person).all()
# 使用filter_by来做条件查询
all_person = session.query(Person).filter_by(name='momo1').all()
# 使用filter来做条件查询
all_person = session.query(Person).filter(Person.name=='momo1').all()
# 使用get方法查找数据,get方法是根据id来查找的,只会返回一条数据或者None
person = session.query(Person).get(primary_key)
## 双主键的查询
aa = session.query(AlgAlarmPush).get((1,12))
# 使用first方法获取结果集中的第一条数据
person = session.query(Person).first()
4. 修改对象:首先从数据库中查找对象,然后将这条数据修改为你想要的数据,最后做commit操作就可以修改数据了。
person = session.query(Person).first()
person.name = 'lulu'
session.commit()
5. 删除对象:将需要删除的数据从数据库中查找出来,然后使用`session.delete`方法将这条数据从session中删除,最后做commit操作就可以了。
person = session.query(Person).first()
session.delete(person)
session.commit()