python使用mysql的兩種使用方式，pythonmysql

python使用mysql的兩種使用方式，pythonmysql

Python操作MySQL主要使用兩種方式：

• 原生模組 pymsql
• ORM架構 SQLAchemy

pymql

pymsql是Python中操作MySQL的模組,在windows中的安裝：

pip install pymysql

入門：我們串連虛擬機器中的centos中的mysql，然後查詢test資料庫中student表的資料

import pymysql#建立串連conn = pymysql.connect(host='192.168.123.207',port=3306,user='root',passwd='root',db="test");#建立遊標cursor = conn.cursor()#執行sql，並返回受影響的行數effect_row = cursor.execute("select * from student")print(effect_row)

運行結果：

 

需要給許可權

mysql> grant all on *.* to 'root'@'%' identified by 'root';Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)

這個時候我們在運行上面的程式我們就可以看到，運行成功

 

這是說明查詢到了5條資料，那如果我們需要查看這五條具體的資料是什麼，我們要用：

print(cursor.fetchone())

cursor.fetchone()是一條一條的把資料取出來

 

這裡我們用了兩條cursor.fetchone()

如果我們想要只取出前3條資料：

print('>>>>>',cursor.fetchmany(3))

 

一次性取出所有資料：

print('------',cursor.fetchall())

 

如果我們要插入多條資料：

import pymysql#建立串連conn = pymysql.connect(host='192.168.123.207',port=3306,user='root',passwd='root',db="test");#建立遊標cursor = conn.cursor()data = [ ("N1",23,"2015-01-01","M"), ("N2",24,"2015-01-02","F"), ("N3",25,"2015-01-03","M"),]#執行sql，並返回受影響的行數effect_row = cursor.executemany("insert into student (name,age,register_date,gender)values(%s,%s,%s,%s)",data)conn.commit()

 

註：executemany預設會啟動一個事務，如果沒有conn.commit()，資料將不會被插入成功

sqlalchemy ORM

1.ORM介紹

orm英文全稱object relational mapping,就是對象映射關係程式，簡單來說類似python這種物件導向的程式來說一切皆對象，我們執行個體化一個對象，通過點的形式來調用裡面的函數。orm相當於把資料庫給執行個體化了，資料庫都是關係型的，通過orm將程式設計語言的物件模型和資料庫的關聯式模式建立映射關係，這樣我們在使用程式設計語言對資料庫進行操作的時候可以直接使用程式設計語言的物件模型進行操作就可以了，而不用直接使用sql語言。

優點：

1.隱藏了資料訪問細節，“封閉”的通用資料庫互動，ORM的核心。他使得我們的通用資料庫互動變得簡單易行，並且完全不用考慮該死的SQL語句

2.ORM使我們構造固化資料結構變得簡單易行

2.sqlalchemy安裝

安裝：

pip install sqlalchemy

 

3.sqlalchemy基本使用

首先我們看一下我們沒有用orm之前我們建立一個資料表是這個樣的：

create table student( id int auto_increment, name char(32) not null, age int not null, register_date date not null, primary key(id) );

使用了orm,實現上面的表的建立，代碼如下：

import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,Stringengine = create_engine("mysql+pymysql://root:root@192.168.123.207/test",encoding='utf-8',echo=True)Base = declarative_base()#產生orm基類class User(Base): __tablename__ = 'user'#表名 id = Column(Integer,primary_key=True) name = Column(String(32)) password = Column(String(64))Base.metadata.create_all(engine)#建立表結構

 

用orm建立一條資料：

import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,Stringfrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+pymysql://root:root@192.168.123.207/test",encoding='utf-8',echo=True)Base = declarative_base()#產生orm基類class User(Base):  __tablename__ = 'user'#表名  id = Column(Integer,primary_key=True)  name = Column(String(32))  password = Column(String(64))Base.metadata.create_all(engine)#建立表結構Session_class = sessionmaker(bind=engine)#建立與資料庫的會話session class ,注意這裡返回給session的是一個類，不是執行個體Session = Session_class()#產生session執行個體user_obj = User(name = "xiaoming" , password = "123456")#產生你要建立的資料對象user_obj2 = User(name = "jack" , password = "123564")#產生你要建立的資料對象print(user_obj.name,user_obj.id)#此時還沒有建立對象，列印一下會發現id還是NoneSession.add(user_obj)Session.add(user_obj2)print(user_obj.name,user_obj.id)#此時也依然還沒有建立

Session.commit()#現在才統一提交，建立資料

插入資料是使用sessionmaker,通過綁定上面建立的串連建立出一個類，產生session執行個體（相當於之前的cursor）,用物件導向的方式建立兩條記錄，然後添加，最後commit就可以了

下面我們來看看資料庫的增刪改查：

查詢：

import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,Stringfrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+pymysql://root:root@192.168.123.207/test",encoding='utf-8')Base = declarative_base()#產生orm基類class User(Base):  __tablename__ = 'user'#表名  id = Column(Integer,primary_key=True)  name = Column(String(32))  password = Column(String(64))Base.metadata.create_all(engine)#建立表結構Session_class = sessionmaker(bind=engine)#建立與資料庫的會話session class ,注意這裡返回給session的是一個類，不是執行個體Session = Session_class()#產生session執行個體data=Session.query(User).filter_by(name="xiaoming").all()#print(data[0].name,data[0].password)print(data)

如果只是Session.query(User).filter_by(name="xiaoming"),只會看到一條sql語句：

filter_by()查出來的是一個列表，是一組資料

加上.all()

 

這個是一個對象，這也看不出來是那個，所以我們要手動去調出資料

我們用print(data[0].name,data[0].password):

 

這樣就查出了資料

現在是查一條資料，如果filter_by()裡面什麼都不寫：

data=Session.query(User).filter_by().all()

 

我們就查出了好幾條資料，我們要迴圈才能看出來具體的資料。我們想要直接看到誰是誰，我們怎麼辦呢？

import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,Stringfrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+pymysql://root:root@192.168.123.207/test",encoding='utf-8')Base = declarative_base()#產生orm基類class User(Base):  __tablename__ = 'user'#表名  id = Column(Integer,primary_key=True)  name = Column(String(32))  password = Column(String(64))  def __repr__(self):    return "<%s name:%s>" % (self.id,self.name)Base.metadata.create_all(engine)#建立表結構Session_class = sessionmaker(bind=engine)#建立與資料庫的會話session class ,注意這裡返回給session的是一個類，不是執行個體Session = Session_class()#產生session執行個體data=Session.query(User).filter_by().all()print(data)

我們添加了__repr__（）函數，這樣看看運行結果：

 

這樣就顯現出我們查詢的結果了。就相當於，如果你要查看什麼資料，以什麼格式的方式顯示，就可以在__repr__這個函數中設定

在這裡，我們用filter_by().all()查詢出所有的資料，那我們用 filter_by().first() ，就可以查詢出資料庫中的第一條資料

上面我們用filter_by(name="xiaoming")查詢出了姓名為xiaoming的資料，那我們想要查詢 使用者id>1的資料 應該怎麼查詢呢？

data=Session.query(User).filter(User.id>1).all()

多個條件查詢：再加幾個filter

data=Session.query(User).filter(User.id>1).filter(User.id<3).all()

修改：

data=Session.query(User).filter(User.id>1).first()print(data)data.name = "Jack Chen"data.password = "555555"Session.commit()

查詢到你要修改的這個資料，然後想修改物件導向裡的資料一樣，對資料進行修改，最後commit（）就可以了

 

刪除：

data = Session.query(User).filter_by(name = 'Rain').first()Session.delete(data)Session.commit()

同樣的，先查詢到要刪除的資料，然後對資料進行刪除，最後提交commit

復原：

fake_user = User(name = 'Rain',password = "123456")Session.add(fake_user)print(Session.query(User).filter(User.name.in_(['jack','rain'])).all())#這時候看看session裡有了剛剛添加的資料Session.rollback()#此時你rollback一下print(Session.query(User).filter(User.name.in_(['jack','rain'])).all())#再查就發現剛剛添加的資料就沒有了

運行結果看看：

 

這個時候可以看到一開始我們是能夠看到剛剛插入的資料，但是復原之後我們就看不到了，我們到資料庫中看看：

 

我們插入一條資料看看

 

可以看出我們插入的資料的id是4，這樣看來確實是先插入進去，然後復原之後刪除的

分組 統計：

統計：

Session.query(User).filter(User.name.in_(['xiaoming','rain'])).count()

分組：

from sqlalchemy import funcprint(Session.query(func.count(User.name),User.name).group_by(User.name).all())

 

join多表查詢:

Session.query(User,Student).filter(User.id == Student.id).all()Session.query(User).join(Student).all()#這種寫法必須要求兩個表有外部索引鍵關聯

 

外部索引鍵關聯

我們先建立兩個表student,study_record：

import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,DATE,ForeignKeyfrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+pymysql://root:root@192.168.123.207/xumingdb",encoding='utf-8')Base = declarative_base()#產生orm基類class Student(Base):  __tablename__ = 'student'#表名  id = Column(Integer,primary_key=True)  name = Column(String(32),nullable=False)  register_date = Column(DATE,nullable=False)  def __repr__(self):    return "<%s name:%s>" % (self.id,self.name)class StudyRecord(Base):  __tablename__ = 'study_record'#表名  id = Column(Integer,primary_key=True)  day = Column(Integer,nullable=False)  status = Column(String(32),nullable=False)  stu_id = Column(Integer,ForeignKey("student.id"))  def __repr__(self):    return "<%s day:%s>" % (self.id,self.day)Base.metadata.create_all(engine)#建立表結構

外鍵表的建立，要用到ForeignKey("student.id")裡面就直接是 表名.欄位名

然後向兩個表中插入資料：

Base.metadata.create_all(engine)#建立表結構Session_class = sessionmaker(bind=engine)session = Session_class()s1 = Student(name = "xiaoming",register_date="2015-06-07")s2 = Student(name = "huahua",register_date="2015-06-08")s3 = Student(name = "caicai",register_date="2015-06-09")s4 = Student(name = "zhazha",register_date="2015-06-04")study_obj1 = StudyRecord(day = 1,status = "YES",stu_id=1)study_obj2 = StudyRecord(day = 2,status = "NO",stu_id=1)study_obj3 = StudyRecord(day = 3,status = "YES",stu_id=1)study_obj4 = StudyRecord(day = 1,status = "YES",stu_id=2)session.add_all([s1,s2,s3,s4])session.add_all([study_obj1,study_obj2,study_obj3,study_obj4])session.commit()

批量插入用add_all，裡面用一個列表放入要插入的資料， 注意的是，因為建立了外鍵，所以在添加資料的時候，study_record的資料一定要在student表資料插入之後才能夠被插入，如果一起插入就會報錯

現在我們要查詢xiaoming一共上了幾節課，那應該怎麼做呢：

import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,DATE,ForeignKeyfrom sqlalchemy.orm import sessionmaker,relationshipengine = create_engine("mysql+pymysql://root:root@192.168.123.207/xumingdb",encoding='utf-8')Base = declarative_base()#產生orm基類class Student(Base):  __tablename__ = 'student'#表名  id = Column(Integer,primary_key=True)  name = Column(String(32),nullable=False)  register_date = Column(DATE,nullable=False)  def __repr__(self):    return "<%s name:%s>" % (self.id,self.name)class StudyRecord(Base):  __tablename__ = 'study_record'#表名  id = Column(Integer,primary_key=True)  day = Column(Integer,nullable=False)  status = Column(String(32),nullable=False)  stu_id = Column(Integer,ForeignKey("student.id"))  student = relationship("Student",backref="my_study_record")  def __repr__(self):    return "<%s day:%s status:%s>" % (self.student.name,self.day,self.status)Base.metadata.create_all(engine)#建立表結構Session_class = sessionmaker(bind=engine)session = Session_class()stu_obj = session.query(Student).filter(Student.name=='xiaoming').first() print(stu_obj.my_study_record) session.commit()

注意上面代碼標紅的這個語句，我們引入了relationship,然後這個允許你在study_record表裡通過backref欄位反向查出所有它在student表裡的關聯項,實現了一個雙向尋找，即關聯student表，在studyrecord中通過student欄位就可以查 student表中的所有欄位，反過來，我們可以在student表中通過 my_study_record欄位反查studyrecord裡面的資料，然後代表著我們在下面查到的xiaoming,可以.my_study_record就可以查詢出在studyrecord表中還有xiaoming的id的資料項目

多外部索引鍵關聯

首先我們建立兩個表customer和address兩個表，其中customer表中有address的兩個外鍵：

import sqlalchemyfrom sqlalchemy import Integer, ForeignKey, String, Columnfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import relationshipfrom sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:root@192.168.123.207/xumingdb",encoding='utf-8')Base = declarative_base()class Customer(Base):  __tablename__ = 'customer'  id = Column(Integer, primary_key=True)  name = Column(String(64))  billing_address_id = Column(Integer, ForeignKey("address.id"))  shipping_address_id = Column(Integer, ForeignKey("address.id"))  billing_address = relationship("Address")  shipping_address = relationship("Address")class Address(Base):  __tablename__ = 'address'  id = Column(Integer, primary_key=True)  street = Column(String(64))  city = Column(String(64))  state = Column(String(64))　　def __repr__(self):  　　return self.streetBase.metadata.create_all(engine) # 建立表結構

然後我們向兩個表中插入資料：

from day12 import sqlalchemy_multi_fkfrom sqlalchemy.orm import sessionmakerSession_class = sessionmaker(bind=sqlalchemy_multi_fk.engine)session = Session_class()addr1 = sqlalchemy_multi_fk.Address(street="XiZangBeiLu",city="ShangHai",state="ShangHai")addr2 = sqlalchemy_multi_fk.Address(street="YuHu",city="XiangTan",state="HuNan")addr3 = sqlalchemy_multi_fk.Address(street="ZhongXinZhen",city="SuQian",state="JiangSu")session.add_all([addr1,addr2,addr3])c1 = sqlalchemy_multi_fk.Customer(name="xiaoming",billing_address=addr1,shipping_address=addr2)c2 = sqlalchemy_multi_fk.Customer(name="jack",billing_address=addr3,shipping_address=addr3)session.add_all([c1,c2])session.commit()

這樣插入會出現一個錯誤：

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Customer.billing_address - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

說白了就是我們現在在做關聯查詢的時候，有兩個欄位同時關聯到了Address表中 ，在做反向查詢的時候它分不清楚誰是誰，通過address反向查的時候分不清哪個欄位代表billing_address,哪個欄位是代表了shipping_address.那我們怎麼解決呢？

billing_address = relationship("Address",foreign_keys=[billing_address_id])shipping_address = relationship("Address",foreign_keys=[shipping_address_id])

在建立反向查詢的語句中添加foreign_keys參數就可以了

 

添加資料成功！！

這個時候我們要查詢customer的地址：

obj = session.query(sqlalchemy_multi_fk.Customer).filter(sqlalchemy_multi_fk.Customer.name=="xiaoming").first()print(obj.name,obj.billing_address,obj.shipping_address)

 

多對多關係

現在讓我們設計一個能描述“圖書”與“作者”的關係的表結構，需求是：

1.一本書可以有好幾個作者一起出版

2.一個作者可以寫好幾本書

首先我們看一下一般的思路 ：

 

兩個表，然後我們在遇到一本書有多個作者共同參與出版，就把作者id寫在一起，但是這樣不利於查詢

那我們可以再添加一張表：

 

這就實現了雙向的一對多，一個作者可以包含多本書，一本書可以包含多個作者。這就形成了多對多。

來看代碼的實現：

首先還是先建立資料表：

from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKeyfrom sqlalchemy.orm import relationshipfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:root@192.168.123.207/xumingdb",encoding='utf-8')Base = declarative_base()Base = declarative_base()book_m2m_author = Table('book_m2m_author', Base.metadata,            Column('book_id',Integer,ForeignKey('books.id')),            Column('author_id',Integer,ForeignKey('authors.id')),            )class Book(Base):  __tablename__ = 'books'  id = Column(Integer,primary_key=True)  name = Column(String(64))  pub_date = Column(DATE)  authors = relationship('Author',secondary=book_m2m_author,backref='books')  def __repr__(self):    return self.nameclass Author(Base):  __tablename__ = 'authors'  id = Column(Integer, primary_key=True)  name = Column(String(32))  def __repr__(self):    return self.nameBase.metadata.create_all(engine) # 建立表結構

這裡我們使用了另外一種建立表的方式，建立了第三張表book_m2m_auther，這張表建立起來後基本上不需要我們去人為的添加資料，對使用者來講是不用關心這裡面有什麼資料，是由orm自動幫你維護的。也就不需要給它建立映射關係了。

但是在mysql端其實已經關聯上了，因為外鍵已經建立好了，在orm查詢的時候，還要做一個orm層級的 記憶體對象的映射：relationship，告訴book表和author表在查詢資料的時候去哪張表裡查詢。

所以看上面的紅色的代碼，通過secondary這個欄位去查第三張表。

這個時候就建立好了多對多的關係。我們就可以插入資料看效果：（先把表建立起來再說）

from day12 import sqlalchemy_multitomultifrom sqlalchemy.orm import sessionmakerSession_class=sessionmaker(bind=sqlalchemy_multitomulti.engine)session = Session_class()b1 = sqlalchemy_multitomulti.Book(name="book1",pub_date="2014-05-16")b2 = sqlalchemy_multitomulti.Book(name="book2",pub_date="2012-03-16")b3 = sqlalchemy_multitomulti.Book(name="book3",pub_date="2016-06-16")a1 = sqlalchemy_multitomulti.Author(name="xiaoming")a2 = sqlalchemy_multitomulti.Author(name="jack") a3 = sqlalchemy_multitomulti.Author(name="Rain")b1.authors = [a1,a3]b2.authors = [a2,a3]b3.authors = [a1,a2,a3]session.add_all([b1,b2,b3,a1,a2,a3])session.commit()

上面紅色標記是建立關聯關係，這樣建立出來之後，book_m2m_author表自動就會有資料。

當然如果我們想要插入中文的書。即插入的資料有中文，我們要怎麼做呢：

engine = create_engine("mysql+pymysql://root:root@192.168.123.207/xumingdb?charset=utf8",encoding='utf-8')Base = declarative_base()

需要在建立資料庫連接的時候在資料庫後面加上 ?charset=utf8

現在資料插入之後，最終要實現查詢資料：

1.查看作者xiaoming出版了多少本書：

author_obj = session.query(sqlalchemy_multitomulti.Author).filter(sqlalchemy_multitomulti.Author.name=="xiaoming").first()print(author_obj.books)

 

2.查看書b2是哪些作者出版的 ：

book_obj = session.query(sqlalchemy_multitomulti.Book).filter(sqlalchemy_multitomulti.Book.id==2).first()print(book_obj.authors)

 

多對多刪除：

在刪除資料的時候我們也同樣的不需要管book_m2m_author表，sqlalchemy會自動幫我們把對應的資料刪除：

通過書刪除作者：

author_obj = session.query(sqlalchemy_multitomulti.Author).filter(sqlalchemy_multitomulti.Author.name=="xiaoming").first()book_obj = session.query(sqlalchemy_multitomulti.Book).filter(sqlalchemy_multitomulti.Book.id==2).first()book_obj.authors.remove(author_obj)session.commit()　

 

這個時候圖書2的關聯關係會自動少一個

直接刪除作者：

author_obj = session.query(sqlalchemy_multitomulti.Author).filter(sqlalchemy_multitomulti.Author.name=="xiaoming").first()session.delete(author_obj)session.commit()

總結

以上所述是小編給大家介紹的python使用mysql的兩種使用方式，希望對大家有所協助，如果大家有任何疑問請給我留言，小編會及時回複大家的。在此也非常感謝大家對幫客之家網站的支援！