Python 12th Day

Python operation MySQL

Import Pymysql
"CREATE DATABASE Connection"
Conn =pymysql.connect (host= ' localhost ', port=3306,user= ' root ', db= ' test ');
' Create cursor '
Cursor =conn.cursor ()
"Execute a single SQL"
Effect_row = Cursor.execute ("INSERT into Oldboydb (name,age,gender) value (' Tianshuai ', ' + ', ' M ')")
"Execute multiple SQL"
Data ={[' Alex ', ' A ', ' m '],[' Jack ', ' All ', ' m ']}
Effect_row = Cursor.executemany (' INSERT into Oldboydb (Name,age,gender) VALUES (%s,%s,%s) ', data)
"' Get a single result '
Print (Conn.fetchone ())
"Get more than one result"
Print (Conn.fetchmany (3))
"Get all Results"
Print (Conn.fetchall ())

"Submit Data"
Conn.commit ()
"' Close database connection '
Conn.close ()

Sqlachemy Module

CREATE TABLE structure

from sqlalchemy  import create_engine from sqlalchemy.ext.declarative  import declarative_base from sqlalchemy  import Column, Integer, String engine  = create_engine( "mysql+pymysql://root:[email protected]/testdb？charset=utf8", echo = True )

Base = Declarative_base () defines a less common way of table structure Book_m2m_author = table (' Book_m2m_author ', Base.metadata,                        Column (' book_id ', Integer,foreignkey (' books.id ')),                        Column (' author_id ', Integer,foreignkey (' authors.id ')),                        )
Common ways to define table structures

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)  #创建表结构

Inserting data

Session_class =sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

Session  = Session_class()  #生成session实例 user_obj  = User(name = "alex" ,password = "alex3714" )  #生成你要创建的数据对象 print (user_obj.name,user_obj. id )   #此时还没创建对象呢，不信你打印一下id发现还是None Session.add(user_obj)  #把要创建的数据对象添加到这个session里， 一会统一创建 print (user_obj.name,user_obj. id )  #此时也依然还没创建 Session.commit()  #现此才统一提交，创建数据Inquire my_user  = Session.query(User).filter_by(name = "alex" ).first()  query()括号内指定查询的表名，filter_by()和filter()括号内指定查询条件，.first输出第一条数据;.all输出所有数据 def __repr__( self ):  在定义表结构的类中加上这个函数就可以让查询结果可视化，引号内的显示内容可以自定义      return "<User(name=‘%s‘,  password=‘%s‘)>" % (          self .name,  self .password) objs  = Session.query(User). filter (User. id > 0 ). filter (User. id < 7 ). all ()  多条件查询Modify my_user  = Session.query(User).filter_by(name = "alex" ).first() my_user.name  = "Alex Li" Session.commit()Rollback Session.rollback () statistics Session.query(User). filter (User.name.like( "Ra%" )).count()Group from sqlalchemy  import func print (Session.query(func.count(User.name),User.name).group_by(User.name). all () )FOREIGN Key Association from sqlalchemy  import ForeignKey from sqlalchemy.orm  import relationship class Address(Base):      __tablename__  = ‘addresses‘      id = Column(Integer, primary_key = True )      email_address  = Column(String( 32 ), nullable = False )      user_id  = Column(Integer, ForeignKey( ‘user.id‘ ))      user  = relationship( "User" , backref = "addresses" )  #这个nb，允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项      def __repr__( self ):          return "<Address(email_address=‘%s‘)>" % self .email_address obj  = Session.query(User).first() for i  in obj.addresses:  #user表通过backref字段的addresses来查询Address表中关联的记录      print (i) addr_obj  = Session.query(Address).first() print (addr_obj.user.name)   #在addr_obj里直接查关联的user表A multi-foreign key association is a table in which more than one field is associated with the same other table from sqlalchemy  import Integer, ForeignKey, String, Column from sqlalchemy.ext.declarative  import declarative_base from sqlalchemy.orm  import relationship Base  = declarative_base() class Customer(Base):      __tablename__  = ‘customer‘      id = Column(Integer, primary_key = True )      name  = Column(String)      billing_address_id  = Column(Integer, ForeignKey( "address.id" ))      shipping_address_id  = Column(Integer, ForeignKey( "address.id" ))      billing_address  = relationship( "Address", foreign_keys=[billing_address_id] )       shipping_address  = relationship( "Address", foreign_keys=[shipping_address_id] ) class Address(Base):      __tablename__  = ‘address‘      id = Column(Integer, primary_key = True )      street  = Column(String)      city  = Column(String)      state  = Column(String)A many-to-many association, where each record in two tables is associated with more than one record in another table

From SQLAlchemy import Table, Column, integer,string,date, foreignkeyfrom sqlalchemy.orm import Relationshipfrom Sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import create_enginefrom sqlalchemy.orm Import Sessionmakerbase = Declarative_base () Book_m2m_author = Table (' Book_m2m_author ', Base.metadata, Colu                         Mn (' 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 = C     Olumn (String) 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, PR Imary_key=true) name = Column (String ()) def __repr__ (self): return self.name

Many-to-many delete records
Delete an author of a book
author_obj =s.query(Author).filter_by(name="Jack").first()

book_obj  = s.query(Book).filter_by(name = "跟Alex学把妹" ).first() book_obj.authors.remove(author_obj)  #从一本书里删除一个作者 s.commit()Delete Author directly author_obj  = s.query(Author).filter_by(name = "Alex" ).first() # print(author_obj.name , author_obj.books) s.delete(author_obj) s.commit()Set database support Chinese eng = create_engine (' Mysql://root:[email Protected]:3306/test2?charset=utf8 ', echo=true)

Python 12th Day