1.pymysql
2.SQLAlchemy
pymsql은 MySQL을 운영하는 모듈이다.
#在终端直接运行 pip3 install pymysql
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql
# 创建连接conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')# 创建游标cursor = conn.cursor()
# 执行SQL，并返回受影响行数effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
# 执行SQL，并返回受影响行数#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,)) # 执行SQL，并返回受影响行数#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
# 提交，不然无法保存新建或者修改的数据conn.commit()
# 关闭游标cursor.close()# 关闭连接conn.close()b를 실행합니다. 참고: 데이터를 가져올 때 순서대로 진행하세요. .cursor.scroll(num, mode)를 사용하여 다음과 같이 커서 위치를 이동할 수 있습니다.
cursor.scroll(1, mode='relative') # 현재 위치를 기준으로 이동
cursor. scroll(2,mode='absolute') # 상대 절대 위치 이동
d.fetch 데이터 유형
입니다.
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()# 获取最新自增IDnew_id = cursor.lastrowid
cursor.close()
conn.close()SQLAlchemy는 Python 프로그래밍 언어를 기반으로 하는 ORM 프레임워크입니다. 이 프레임워크는 데이터베이스 API를 기반으로 구축되었으며 간단히 말해서 개체를 SQL로 변환한 다음 데이터를 사용합니다. SQL을 실행하고 실행 결과를 얻기 위한 API입니다.
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts")
# 获取第一行数据row_1 = cursor.fetchone()
# 获取前n行数据# row_2 = cursor.fetchmany(3)# 获取所有数据# row_3 = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()2. SQLAlchemy 종속성
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 游标设置为字典类型cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()")
result = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()
ORM/스키마 유형/SQL 표현식 언어/엔진/ConnectionPooling/Dialect를 사용하여 데이터를 작동합니다. 클래스를 기반으로 객체를 생성하고, 객체를 SQL로 변환하고, SQL을 실행합니다. a. 테이블 만들기
#在终端直接运行pip3 install SQLAlchemy
更多详见：index.html참고: 외래 키를 설정하는 또 다른 방법은 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
b.1 추가
#!/usr/bin/env python# -*- coding:utf-8 -*-from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import create_engine#表明依赖关系并创建连接，最大连接数为5 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
Base = declarative_base()
# 创建单表class Users(Base):
__tablename__ = 'users' # 表名 id = Column(Integer, primary_key=True,autoincrement=True) # id列，主键自增 name = Column(String(32)) # name列 extra = Column(String(16)) # extra列
__table_args__ = (
UniqueConstraint('id', 'name', name='uix_id_name'), # 创建联合唯一索引 Index('ix_id_name', 'name', 'extra'), # 创建普通索引 )
# 一对多class Favor(Base):
__tablename__ = 'favor' nid = Column(Integer, primary_key=True)
caption = Column(String(50), default='red', unique=True)
class Person(Base):
__tablename__ = 'person' nid = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=True)
favor_id = Column(Integer, ForeignKey("favor.nid")) # 创建外键
# 多对多class Group(Base):
__tablename__ = 'group' id = Column(Integer, primary_key=True)
name = Column(String(64), unique=True, nullable=False)
port = Column(Integer, default=22)
class Server(Base):
__tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True)
hostname = Column(String(64), unique=True, nullable=False)
class ServerToGroup(Base):
__tablename__ = 'servertogroup' nid = Column(Integer, primary_key=True, autoincrement=True)
server_id = Column(Integer, ForeignKey('server.id')) # 创建外键 group_id = Column(Integer, ForeignKey('group.id')) # 创建外键
def init_db():
Base.metadata.create_all(engine)
def drop_db():
Base.metadata.drop_all(engine)#!/usr/bin/env python# -*- coding:utf-8 -*-from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
Base = declarative_base()# 创建单表class Users(Base):__tablename__ = 'users'id = Column(Integer, primary_key=True)
name = Column(String(32))
extra = Column(String(16))__table_args__ = (
UniqueConstraint('id', 'name', name='uix_id_name'),
Index('ix_id_name', 'name', 'extra'),
)def __repr__(self):return "%s-%s" %(self.id, self.name)# 一对多class Favor(Base):__tablename__ = 'favor'nid = Column(Integer, primary_key=True)
caption = Column(String(50), default='red', unique=True)def __repr__(self):return "%s-%s" %(self.nid, self.caption)class Person(Base):__tablename__ = 'person'nid = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=True)
favor_id = Column(Integer, ForeignKey("favor.nid"))# 与生成表结构无关，仅用于查询方便favor = relationship("Favor", backref='pers')# 多对多class ServerToGroup(Base):__tablename__ = 'servertogroup'nid = Column(Integer, primary_key=True, autoincrement=True)
server_id = Column(Integer, ForeignKey('server.id'))
group_id = Column(Integer, ForeignKey('group.id'))
group = relationship("Group", backref='s2g')
server = relationship("Server", backref='s2g')class Group(Base):__tablename__ = 'group'id = Column(Integer, primary_key=True)
name = Column(String(64), unique=True, nullable=False)
port = Column(Integer, default=22)# group = relationship('Group',secondary=ServerToGroup,backref='host_list')class Server(Base):__tablename__ = 'server'id = Column(Integer, primary_key=True, autoincrement=True)
hostname = Column(String(64), unique=True, nullable=False)def init_db():
Base.metadata.create_all(engine)def drop_db():
Base.metadata.drop_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
b.3 변경 #单条增加obj = Users(name="alex0", extra='sb') session.add(obj)#多条增加session.add_all([ Users(name="alex1", extra='sb'), Users(name="alex2", extra='sb'), ])#提交session.commit()
#先查询到要删除的记录，再deletesession.query(Users).filter(Users.id > 2).delete() session.commit()
b.5 기타
#先查询，再更新session.query(Users).filter(Users.id > 2).update({"name" : "099"}) # 直接更改session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False) # 字符串拼接session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate") # 数字相加session.commit()1. : MySQL을 운영하는 Python
위 내용은 MySQL— pymysql 및 SQLAlchemy의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
