建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
Python操作MySQL數(shù)據(jù)庫的三個(gè)模塊
?python使用MySQL主要有兩個(gè)模塊，pymysql（MySQLdb）和SQLAchemy。
為信陽等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計(jì)制作服務(wù)，及信陽網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都網(wǎng)站制作、網(wǎng)站建設(shè)、信陽網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長期合作。這樣，我們也可以走得更遠(yuǎn)！
- pymysql（MySQLdb）為原生模塊，直接執(zhí)行sql語句，其中pymysql模塊支持python 2和python3，MySQLdb只支持python2，兩者使用起來幾乎一樣。
- SQLAchemy為一個(gè)ORM框架，將數(shù)據(jù)對(duì)象轉(zhuǎn)換成SQL，然后使用數(shù)據(jù)API執(zhí)行SQL并獲取執(zhí)行結(jié)果
- 另外DBUtils模塊提供了一個(gè)數(shù)據(jù)庫連接池，方便多線程場景中python操作數(shù)據(jù)庫。
1.pymysql模塊
安裝：pip install pymysql
創(chuàng)建表格操作(注意中文格式設(shè)置)
#coding:utf-8
import pymysql
#關(guān)于中文問題
#1. mysql命令行創(chuàng)建數(shù)據(jù)庫，設(shè)置編碼為gbk：create databse demo2 character set utf8;
#2. python代碼中連接時(shí)設(shè)置charset="gbk"
#3. 創(chuàng)建表格時(shí)設(shè)置default charset=utf8
#連接數(shù)據(jù)庫
conn = pymysql.connect(host="localhost", user="root", passwd="", db='learningsql', charset='utf8', port=3306) #和mysql服務(wù)端設(shè)置格式一樣（還可設(shè)置為gbk, gb2312）
#創(chuàng)建游標(biāo)
cursor = conn.cursor()
#執(zhí)行sql語句
cursor.execute("""create table if not exists t_sales(
id int primary key auto_increment not null,
nickName varchar(128) not null,
color varchar(128) not null,
size varchar(128) not null,
comment text not null,
saledate varchar(128) not null)engine=InnoDB default charset=utf8;""")
# cursor.execute("""insert into t_sales(nickName,color,size,comment,saledate)
# values('%s','%s','%s','%s','%s');""" % ("zack", "黑色", "L", "大小合適", "2019-04-20"))
cursor.execute("""insert into t_sales(nickName,color,size,comment,saledate)
values(%s,%s,%s,%s,%s);""" , ("zack", "黑色", "L", "大小合適", "2019-04-20"))
#提交
conn.commit()
#關(guān)閉游標(biāo)
cursor.close()
#關(guān)閉連接
conn.close()
增刪改查：
注意execute執(zhí)行sql語句參數(shù)的兩種情況：
- execute("insert into t_sales(nickName, size) values('%s','%s');" % ("zack","L") ) #此時(shí)的%s為字符竄拼接占位符，需要引號(hào)加'%s' （有sql注入風(fēng)險(xiǎn)）
- execute("insert into t_sales(nickName, size) values(%s,%s);" , ("zack","L") ) #此時(shí)的%s為sql語句占位符，不需要引號(hào)%s
#***************************增刪改查******************************************************
conn = pymysql.connect(host="localhost", user="root", passwd="", db='learningsql', charset='utf8', port=3306) #和mysql服務(wù)端設(shè)置格式一樣（還可設(shè)置為gbk, gb2312）
#創(chuàng)建游標(biāo)
cursor = conn.cursor()
insert_sql = "insert into t_sales(nickName,color,size,comment,saledate) values(%s,%s,%s,%s,%s);"
#返回受影響的行數(shù)
row1 = cursor.execute(insert_sql,("Bob", "黑色", "XL", "便宜實(shí)惠", "2019-04-20"))
update_sql = "update t_sales set color='白色' where id=%s;"
#返回受影響的行數(shù)
row2 = cursor.execute(update_sql,(1,))
select_sql = "select * from t_sales where id>%s;"
#返回受影響的行數(shù)
row3 = cursor.execute(select_sql,(1,))
delete_sql = "delete from t_sales where id=%s;"
#返回受影響的行數(shù)
row4 = cursor.execute(delete_sql,(4,))
#提交，不然無法保存新建或者修改的數(shù)據(jù)(增刪改得提交)
conn.commit()
cursor.close()
conn.close()
批量插入和自增id
#***************************批量插入******************************************************
conn = pymysql.connect(host="localhost", user="root", passwd="", db='learningsql', charset='utf8', port=3306) #和mysql服務(wù)端設(shè)置格式一樣（還可設(shè)置為gbk, gb2312）
#創(chuàng)建游標(biāo)
cursor = conn.cursor()
insert_sql = "insert into t_sales(nickName,color,size,comment,saledate) values(%s,%s,%s,%s,%s);"
data = [("Bob", "黑色", "XL", "便宜實(shí)惠", "2019-04-20"),("Ted", "黃色", "M", "便宜實(shí)惠", "2019-04-20"),("Gary", "黑色", "M", "穿著舒服", "2019-04-20")]
row1 = cursor.executemany(insert_sql, data)
conn.commit()
#為插入的第一條數(shù)據(jù)的id，即插入的為5,6,7，new_id=5
new_id = cursor.lastrowid
print(new_id)
cursor.close()
conn.close()
獲取查詢數(shù)據(jù)
#***************************獲取查找sql的查詢數(shù)據(jù)******************************************************
conn = pymysql.connect(host="localhost", user="root", passwd="", db='learningsql', charset='utf8', port=3306) #和mysql服務(wù)端設(shè)置格式一樣（還可設(shè)置為gbk, gb2312）
#創(chuàng)建游標(biāo)
cursor = conn.cursor()
select_sql = "select id,nickname,size from t_sales where id>%s;"
cursor.execute(select_sql, (3,))
row1 = cursor.fetchone() #獲取第一條數(shù)據(jù)，獲取后游標(biāo)會(huì)向下移動(dòng)一行
row_n = cursor.fetchmany(3) #獲取前n條數(shù)據(jù)，獲取后游標(biāo)會(huì)向下移動(dòng)n行
row_all = cursor.fetchall() #獲取所有數(shù)據(jù)，獲取后游標(biāo)會(huì)向下移動(dòng)到末尾
print(row1)
print(row_n)
print(row_all)
#conn.commit()
cursor.close()
conn.close()
注：在fetch數(shù)據(jù)時(shí)按照順序進(jìn)行，可以使用cursor.scroll(num,mode)來移動(dòng)游標(biāo)位置，如：
- cursor.scroll(1,mode='relative') # 相對(duì)當(dāng)前位置移動(dòng)
- cursor.scroll(2,mode='absolute') # 相對(duì)絕對(duì)位置移動(dòng)
fetch獲取數(shù)據(jù)類型
fetch獲取的數(shù)據(jù)默認(rèn)為元組格式，還可以獲取字典類型的，如下：
#***************************獲取字典格式數(shù)據(jù)******************************************************
conn = pymysql.connect(host="localhost", user="root", passwd="", db='learningsql', charset='utf8', port=3306) #和mysql服務(wù)端設(shè)置格式一樣（還可設(shè)置為gbk, gb2312）
#創(chuàng)建游標(biāo)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
select_sql = "select id,nickname,size from t_sales where id>%s;"
cursor.execute(select_sql, (3,))
row1 = cursor.fetchall()
print(row1)
conn.commit()
cursor.close()
conn.close()
2.SQLAlchmy框架
SQLAlchemy的整體架構(gòu)如下，建立在第三方的DB API上，將類和對(duì)象操作轉(zhuǎn)換為數(shù)據(jù)庫sql，然后利用DB API執(zhí)sql語句得到結(jié)果。其適用于多種數(shù)據(jù)庫。另外其內(nèi)部實(shí)現(xiàn)了數(shù)據(jù)庫連接池，方便進(jìn)行多線程操作。
- Engine，框架的引擎
- Connection Pooling ，數(shù)據(jù)庫連接池
- ??Dialect???，選擇連接數(shù)據(jù)庫的DB API種類，（pymysql，mysqldb等）``
- Schema/Types，架構(gòu)和類型
- SQL Exprression Language，SQL表達(dá)式語言
- ??DB API??：Python Database API Specification
2.1 執(zhí)行原生sql
安裝：pip install sqlalchemy
SQLAlchmy也可以不利用ORM，使用數(shù)據(jù)庫連接池，類似pymysql模塊執(zhí)行原生sql。
#coding:utf-8
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer
import threading
engine = create_engine(
"mysql+pymysql://root@127.0.0.1:3306/learningsql?charset=utf8",
max_overflow = 0, #超過連接池大小外最多創(chuàng)建的連接，為0表示超過5個(gè)連接后，其他連接請(qǐng)求會(huì)阻塞 （默認(rèn)為10）
pool_size = 5, #連接池大?。J(rèn)為5）
pool_timeout = 30, #連接線程池中，沒有連接時(shí)最多等待的時(shí)間，不設(shè)置無連接時(shí)直接報(bào)錯(cuò) （默認(rèn)為30）
pool_recycle = -1) #多久之后對(duì)線程池中的線程進(jìn)行一次連接的回收（重置） （默認(rèn)為-1）
# def task():
# conn= engine.raw_connection() #建立原生連接,和pymysql的連接一樣
# cur = conn.cursor()
# cur.execute("select * from t_sales where id>%s",(2,))
# result = cur.fetchone()
# cur.close()
# conn.close()
# print(result)
# def task():
# conn = engine.contextual_connect() #建立上下文管理器連接，自動(dòng)打開和關(guān)閉
# with conn:
# cur = conn.execute("select * from t_sales where id>%s",(2,))
# result = cur.fetchone()
# print(result)
def task():
cur =engine.execute("select * from t_sales where id>%s",(2,)) #engine直接執(zhí)行
result = cur.fetchone()
cur.close()
print(result)
if __name__=="__main__":
for i in range(10):
t = threading.Thread(target=task)
t.start()
2.2 執(zhí)行ORM語句
A. 創(chuàng)建和刪除表
#coding:utf-8
import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, DateTime, Text
Base = declarative_base()
class User(Base):
__tablename__="users"
id = Column(Integer,primary_key=True)
name = Column(String(32),index=True, nullable=False) #創(chuàng)建索引，不為空
email = Column(String(32),unique=True)
ctime = Column(DateTime, default = datetime.datetime.now) #傳入方法名datetime.datetime.now
extra = Column(Text,nullable=True)
__table_args__ = {
# UniqueConstraint('id', 'name', name='uix_id_name'), #設(shè)置聯(lián)合唯一約束
# Index('ix_id_name', 'name', 'email'), # 創(chuàng)建索引
}
def create_tbs():
engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/learningsql?charset=utf8",max_overflow=2,pool_size=5)
Base.metadata.create_all(engine) #創(chuàng)建所有定義的表
def drop_dbs():
engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/learningsql?charset=utf8",max_overflow=2,pool_size=5)
Base.metadata.drop_all(engine) #刪除所有創(chuàng)建的表
if __name__=="__main__":
create_tbs() #創(chuàng)建表
#drop_dbs() #刪除表
B.表中定義外鍵關(guān)系（一對(duì)多，多對(duì)多）
思考：下面代碼中的一對(duì)多關(guān)系，relationship 定義在了 customer 表中，應(yīng)該定義在 PurchaseOrder 更合理？
注意：mysql 數(shù)據(jù)庫中避免使用 order做為表的名字，order 為一個(gè) mysql 關(guān)鍵字，做為表名字時(shí)必須用反引號(hào)order (鍵盤數(shù)字1旁邊的符號(hào))。
#coding:utf-8
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,Text,DateTime,ForeignKey,Float
from sqlalchemy.orm import relationship
import datetime
engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/learningsql?charset=utf8") #數(shù)據(jù)庫有密碼時(shí)，//root:12345678@127.0.0.1:3306/
Base = declarative_base()
class Customer(Base):
__tablename__="customer" #數(shù)據(jù)庫中保存的表名字
id = Column(Integer,primary_key=True)
name = Column(String(64),index=True,nullable=False)
phone = Column(String(16),nullable=False)
address = Column(String(256),nullable=False)
purchase_order_id = Column(Integer,ForeignKey("purchase_order.id")) #關(guān)鍵關(guān)系，關(guān)聯(lián)表的__tablename__="purchase_order"
# 和建立表結(jié)構(gòu)無關(guān)，方便外鍵關(guān)系查詢，backref反向查詢時(shí)使用order_obj.customer
purchase_order = relationship("PurchaseOrder",backref="customer")
class PurchaseOrder(Base):
__tablename__ = "purchase_order" #mysql數(shù)據(jù)庫中表的名字避免使用order，order為一個(gè)關(guān)鍵字，使用時(shí)必須用反引號(hào)`order` (鍵盤數(shù)字1旁邊的符號(hào))
id=Column(Integer,primary_key=True)
cost = Column(Float,nullable=True)
ctime = Column(DateTime,default =datetime.datetime.now)
desc = Column(String(528))
#多對(duì)多關(guān)系時(shí)，secondary為中間表
product = relationship("Product",secondary="order_to_product",backref="purchase_order")
class Product(Base):
__tablename__ = "product"
id = Column(Integer,primary_key=True)
name = Column(String(256))
price = Column(Float,nullable=False)
class OrdertoProduct(Base):
__tablename__ = "order_to_product"
id = Column(Integer,primary_key=True)
product_id = Column(Integer,ForeignKey("product.id"))
purchase_order_id = Column(Integer,ForeignKey("purchase_order.id"))
if __name__ == "__main__":
Base.metadata.create_all(engine)
#Base.metadata.drop_all(engine)
C.增刪改查操作
增刪改查
#coding:utf-8
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,Text,DateTime,ForeignKey,Float
from sqlalchemy.orm import relationship,sessionmaker
from sqlalchemy.sql import text
import datetime
engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/learningsql?charset=utf8") #數(shù)據(jù)庫有密碼時(shí)，//root:12345678@127.0.0.1:3306/, 設(shè)置utf8防止中文亂碼
Base = declarative_base()
class Customer(http://www.5511xx.com/article/cdgppdj.html
