大纲数据库SQL操作ORM实践数据库数据库包括关系型数据库和非关系型数据库。关系型：SQLite、MySQL、Oracle、SqlServer等非关系型：Hbase、MonogoDB、Elasticsearch、redis等今天介绍的是关系型数据库的操作。关系数据库数据库：包含多个数据表。数据表：包含行[记录]和列[字段]。列字段：表示用于描述记录的属性。行记录：由多个字段描述的一条数据。两者都是使用SQL语句进行查询。数据库工具：DBeaver，一个免费的通用数据库工具。下载地址：https://dbeaver.io/download/安装完成后，打开后不要打开任何数据库。当您看到下面的对话框时，单击是。然后工具会自动帮我们创建一个SQLite数据库作为例子。安装驱动：双击数据库下载并安装驱动。有时下载速度很慢或下载失败，需要更换国内源。DBeaver使用Java开发，驱动使用Java包管理maven。如果速度慢，可以点击下载配置。选择Maven=>添加。进入http://maven.aliyun.com/nexus/content/groups/public/，然后选择刚刚添加的source，点up到最上面，最后重启DBeaver。修改后创建数据库连接：Database=>NewConnection=>选择数据库类型。SQLSQL语句是操作数据库的语言。这里只是简单介绍一下。包括INSERT[插入]、SELECT[查询]、UPDATE[更新]、DELETE[删除]。INSERTINSERTINTOfoo(bar,baz)VALUES(1,'a');INSERTINTO表名（字段1，字段2，...）VAULES（值1，值2，...）；UPDATE:UPDATEfooSETbaz='bb'WHEREbar=1;UPDATEtablenameSETfield=newvalueWHERE条件字段1=value1,conditionfield2=value2...;DELETE:DELETEFROMfooWHEREbar=1;DELETEFROM表名WHERE条件字段1=值1,条件字段2=值2...;SELECT:查询所有SELECT*FROMArtist;条件查询SELECT*FROMTrackWHEREAlbumId=1;部分字段SELECTTrackId,NameFROMTrackWHEREAlbumId=1;查询排序SELECT*FROMTrackWHEREAlbumId=1ORDERBYBytes;SELECT*FROMTrackWHEREAlbumId=1ORDERBYBytesDESC;SELECT*/字段FROM表名WHERE条件ORDERBY排序字段；关系型：每张表都有一个主键来唯一标识这条记录，比如Track表中的TrackId，Album表中的AlbumId。外键表示表之间的关系。Track表中的AlbumId是一个外键，它与Album表中的一条数据相关联。Album表中的ArtistId与Artist表关联。主键和外键一般都是数字，可以有效减少存储空间，方便数据修改。JOIN：合并相关表进行查询。SELECTt.Name,a.TitleFROMTracktJOINAlbumaWHEREt.AlbumId=a.AlbumId;JOIN前后连接两张表，t和a是为了方便使用的别名。多对多关系：一个表包含两个外键，使两个表形成多对多关系。即一个Playlist中有多个Track，一个Track也在多个Playlist中。SELECTp.NamePlaylist,t.NameTrackFROMPlaylistpJOINPlaylistTrackptJOINTracktONpt.PlaylistId=p.PlaylistIdANDpt.TrackId=t.TrackId;操作SQLite：内置sqlite3库。SQLite数据库是一个文件，我们在右键编辑连接中找到路径。【记得把\改成\\】创建连接#数据库地址dbfile="xxx.db"conn=sqlite3.connect(dbfile)#连接数据库增删改查：cursor=conn.cursor()#数据库指针，用于操作#执行语句cursor.execute("INSERTINTOfoo(bar,baz)VALUES(1,'a');")ifcursor.rowcount>0:#删除和修改的行数增加print('modified'+str(cursor.rowcount)+'line')conn.commit()#执行cursor.close()#关闭指针每次操作前必须创建指针游标。之后执行指针的execute方法，直接将SQL语句写入其中。可以执行更多的SQL语句。rowcount用于判断表的内容是否被修改，大于0则有修改。最后conn.commit方法执行语句。使用指针后，关闭指针cursor.close。关闭连接：当不需要数据库时，应该关闭数据库。conn.close()#关闭连接查询：cursor=conn.cursor()cursor.execute("SELECT*FROMfoo;")result=cursor.fetchall()print(result)cursor.close()#关闭指针通过fetchall方法获取查询结果，并将结果以列表的形式返回。最后，指针和连接也必须关闭。参数格式：执行SQL时，也可以写成类似的格式。cursor.execute("SELECT*FROMTrackWHEREAlbumId=?ANDTrackId>?;",(1,1))用问号代替条件的值，第二个参数写一个元组，包含具体的值for顺序替换问号。MySQL：pymysql模块。importpymysqlconn=pymysql.connect(host='127.0.0.1',#数据库地址port=3306,#数据库端口user='root',#用户名password='123456',#密码db='test1',#数据库名称)除了使用不同的模块和创建相应的连接外，其余操作相同。ORMORM是一种面向对象的数据库操作方法。最近，ORM成为一种很常用的方法，因为它比较简单，不需要写任何SQL语句。Python中的主要ORM框架是SQLAlchemy和Django中的ORM。这里介绍SQLAlchemy，Django课程中介绍Django的ORM。SQLAlchemy：sqlalchemy模块。创建连接，使用create_engine方法创建对应的引擎。fromsqlalchemyimportcreate_engineengine=create_engine('sqlite:///file.db')#sqlite3engine=create_engine('mysql+pymsyql://root:123456@127.0.0.1/test')#mysql参数使用数据库连接字符串。创建表的基类：这是数据库中所有表的父类。fromsqlalchemy.ext.declarativeimportdeclarative_baseBase=declarative_base()创建表：fromsqlalchemyimportColumn,String,Integer#导入字段定义类classFoo(Base):#继承基类__tablename__='foo'#定义表名bar=Column(Integer,primary_key=True)#定义字段，数字，必须定义主键baz=Column(String(20))#20-lengthstringColumn定义字段，参数1定义字段类型，primary_key定义主键钥匙。[必须有主键]补充：任何操作前，必须先创建一个Session类。Session=sessionmaker(bind=engine)#创建Session类session=Session()#创建sessionfoo=Foo(bar=1,baz='a')#添加新数据session.add(foo)#添加session.commit()#执行session.close()#关闭session等新数据，完全不需要写SQL，直接对对象进行操作即可。新建一个Foo对象就是要添加一段数据，然后添加执行。Foo的参数是我们之前定义的字段。修改：session=Session()foo=session.query(Foo).filter_by(bar=1).first()#条件查询foo.baz='bb'#直接修改属性session.add(foo)#添加保存session.commit()session.close()修改前需要用query查询Foo表，filter_by是条件语句，lastfirst表示获取一个。另一个all方法获取所有满足条件的数据并返回一个列表。找到查询后，直接修改属性，然后保存。效率上比直接SQL慢，但是开发简单，可以在大多数场合使用。删除：session=Session()foo=session.query(Foo).filter_by(bar=1).delete()session.commit()session.close()和修改一样，我们要先查询，这里我们使用delete方法删除满足条件的数据。查询：先定义几个表。艺术家表类Artist(Base):__tablename__='Artist'ArtistId=Column(Integer,primary_key=True,nullable=False)Name=Column(String(120))Album表类Album(Base):__tablename__='Album'AlbumId=Column(Integer,primary_key=True,nullable=False)Title=Column(String(160),nullable=False)ArtistId=Column(Integer,nullable=False)Track表类Track(Base):__tablename__='Track'TrackId=Column(Integer,primary_key=True,nullable=False)Name=Column(String(200),nullable=False)AlbumId=Column(Integer)Bytes=Column(Integer)查询代码session=Session()artists=session.query(Artist).all()#查询全部tracks=session.query(Track).filter_by(AlbumId=1).all()#查询全部tracks=session.query(Track).with_entities(Track.TrackId,Track.Name).filter_by(AlbumId=1).all()#部分字节tracks=session.query(Track).order_by(Track.Bytes).filter_by(AlbumId=1).all()#排序session.close()filter_by定义条件语句，with_entities指指定返回字段，order_by排序外键：ForeignKey。fromsqlalchemyimportForeignKeyfromsqlalchemy.ormimportrelationshipclassAlbum(Base):#外键ArtistId=Column(Integer,ForeignKey('Artist.ArtistId'),nullable=False)Artist=relationship("Artist",backref="Album")classTrack(Base):#外键AlbumId=Column(Integer,ForeignKey('Album.AlbumId'))Album=relationship("Album",backref="Track")Column的第二个参数定义了外键ForeignKey类,它的参数是关联的表名。场地。然后定义一个实体关联一个外键，关系（associationtable,backreef=thistable）。查询的时候可以直接拿到外键的entity。无需加入即可获取外键信息。session=Session()album=session.query(Album).first(artist=album.Artist#外键实体print(artist.Name)session.close()SELECTt.Name,a.TitleFROMTracktJOINAlbumaWHEREt.AlbumId=a.AlbumId;实现这个SQLsession=Session()tracks=session.query(Track).all()ret=[]fortrackintracks:ret.append((track.Name,track.Album.Title))print(ret)session.close()多对多：先添加Playlist表，定义Tracks字段。classPlaylist(Base):__tablename__='Playlist'PlaylistId=Column(Integer,primary_key=True,nullable=False)Name=Column(String(200),nullable=False)#多对多Tracks=relationship("Track",backref="Playlist",secondary=playlist_track)和外键的唯一区别就是多了一个secondary参数，playlist_track就是我们要定义的中间关联表。中间关联表：fromsqlalchemyimportTableplaylist_track=Table("PlaylistTrack",#表名Base.metadata,#表类型Column("PlaylistId",Integer,ForeignKey("Playlist.PlaylistId"),primary_key=True),Column("TrackId",Integer,ForeignKey("Track.TrackId"),primary_key=True))使用Table直接定义表，定义两个外键，作为联合主键。多对多查询：session=Session()playlist=session.query(Playlist).first()print(playlist.Name)fortrackinplaylist.Tracks:print('\t{}【album:{}】【author:{}]'.format(track.Name,track.Album.Title,track.Album.Artist.Name))session.close()通过这个方法，我们不仅可以方便的获取两个表之间的关联,也不需要复杂的JOIN操作就可以很方便的获取更多的关联。多对多增加：session=Session()playlist=session.query(Playlist).filter_by(PlaylistId=2).first()track=session.query(Track).filter_by(TrackId=3503).first()播放列表。tracks.append(track)session.add(playlist)session.commit()session.close()查询获取要添加的播放列表和添加的曲目。直接通过playlist.Tracks.append添加，然后保存执行。习题1、自己练习SQL，用Python实现。2、尝试补全Sample数据库的所有ORM表，查询一个Artist所有专辑的所有Track的所有购买信息。github:https://github.com/lvancer/course_python
