http://python101.pythonlibrary.org/chapter18_sqlite.html
SQLite是一个独立的，无服务器的，无配置的事务型SQL数据库引擎
SQLite仅支持五种数据类型:null, integer, real, text and blob
import sqlite3
conn = sqlite3.connect("mydatabase.db") # or use :memory: to put it in RAM
cursor = conn.cursor()
# create a table
cursor.execute("""CREATE TABLE albums
(title text, artist text, release_date text,
publisher text, media_type text)
""")
# insert some data
cursor.execute("""INSERT INTO albums
VALUES ('Glow', 'Andy Hunter', '7/24/2012',
'Xplore Records', 'MP3')"""
)
# save data to database
conn.commit()
------------------------------------------
# insert multiple records using the more secure "?" method
albums = [('Exodus', 'Andy Hunter', '7/9/2002', 'Sparrow Records', 'CD'),
('Until We Have Faces', 'Red', '2/1/2011', 'Essential Records', 'CD'),
('The End is Where We Begin', 'Thousand Foot Krutch', '4/17/2012', 'TFKmusic', 'CD'),
('The Good Life', 'Trip Lee', '4/10/2012', 'Reach Records', 'CD')]
cursor.executemany("INSERT INTO albums VALUES (?,?,?,?,?)", albums)
conn.commit()
---------------------------------------
import sqlite3
conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()
sql = """
UPDATE albums
SET artist = 'John Doe'
WHERE artist = 'Andy Hunter'
"""
cursor.execute(sql)
conn.commit()
---------------------------------------
import sqlite3
conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()
sql = """
DELETE FROM albums
WHERE artist = 'John Doe'
"""
cursor.execute(sql)
conn.commit()
--------------------------------
import sqlite3
conn = sqlite3.connect("mydatabase.db")
#conn.row_factory = sqlite3.Row
cursor = conn.cursor()
sql = "SELECT * FROM albums WHERE artist=?"
cursor.execute(sql, [("Red")])
print(cursor.fetchall()) # or use fetchone()
print("\nHere's a listing of all the records in the table:\n")
for row in cursor.execute("SELECT rowid, * FROM albums ORDER BY artist"):
print(row)
print("\nResults from a LIKE query:\n")
sql = """
SELECT * FROM albums
WHERE title LIKE 'The%'"""
cursor.execute(sql)
print(cursor.fetchall())
我们执行SQL并使用fetchall（）返回所有结果。您也可以使用fetchone（）获取第一个结果。您还将注意到，其中有一个与神秘的row_factory相关的注释掉的部分。如果您取消注释该行，则结果将作为类似于Python字典的Row对象返回，使您可以像字典一样访问该行的字段。但是，您不能使用Row对象进行项目分配
--------------------------------------------------
SQLAlchemy
https://www.sqlalchemy.org/
http://zetcode.com/db/sqlalchemy/intro/
https://www.pythoncentral.io/introductory-tutorial-python-sqlalchemy/
SQLAlchemy是Python SQL工具箱和对象关系映射器，它为应用程序开发人员提供了SQL的全部功能和灵活性。它提供了一整套众所周知的企业级持久性模式，旨在用于高效和高性能的数据库访问，并被适配为简单的Pythonic域语言
pip install sqlalchemy# Create a Database # table_def.py from sqlalchemy import create_engine, ForeignKey from sqlalchemy import Column, Date, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship, backref engine = create_engine('sqlite:///mymusic.db', echo=True) Base = declarative_base() class Artist(Base): """""" __tablename__ = "artists" id = Column(Integer, primary_key=True) name = Column(String) class Album(Base): """""" __tablename__ = "albums" id = Column(Integer, primary_key=True) title = Column(String) release_date = Column(Date) publisher = Column(String) media_type = Column(String) artist_id = Column(Integer, ForeignKey("artists.id")) artist = relationship("Artist", backref=backref("albums", order_by=id)) # create tables Base.metadata.create_all(engine) # Insert / Add Data to Your Tables # add_data.py import datetime from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from table_def import Album, Artist engine = create_engine('sqlite:///mymusic.db', echo=True) # create a Session Session = sessionmaker(bind=engine) session = Session() # Create an artist new_artist = Artist(name="Newsboys") new_artist.albums = [Album(title="Read All About It", release_date=datetime.date(1988,12,1), publisher="Refuge", media_type="CD")] # add more albums more_albums = [Album(title="Hell Is for Wimps", release_date=datetime.date(1990,7,31), publisher="Star Song", media_type="CD"), Album(title="Love Liberty Disco", release_date=datetime.date(1999,11,16), publisher="Sparrow", media_type="CD"), Album(title="Thrive", release_date=datetime.date(2002,3,26), publisher="Sparrow", media_type="CD")] new_artist.albums.extend(more_albums) # Add the record to the session object session.add(new_artist) # commit the record the database session.commit() # Add several artists session.add_all([ Artist(name="MXPX"), Artist(name="Kutless"), Artist(name="Thousand Foot Krutch") ]) session.commit() # Modify Records with SQLAlchemy # modify_data.py from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from table_def import Album, Artist engine = create_engine('sqlite:///mymusic.db', echo=True) # create a Session Session = sessionmaker(bind=engine) session = Session() # querying for a record in the Artist table res = session.query(Artist).filter(Artist.name=="Kutless").first() print(res.name) # changing the name res.name = "Beach Boys" session.commit() # editing Album data artist, album = session.query(Artist, Album).filter( Artist.id==Album.artist_id).filter(Album.title=="Thrive").first() album.title = "Step Up to the Microphone" session.commit() # Delete Records in SQLAlchemy # deleting_data.py from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from table_def import Album, Artist engine = create_engine('sqlite:///mymusic.db', echo=True) # create a Session Session = sessionmaker(bind=engine) session = Session() res = session.query(Artist).filter(Artist.name=="MXPX").first() session.delete(res) session.commit() # The Basic SQL Queries of SQLAlchemy # queries.py from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from table_def import Album, Artist engine = create_engine('sqlite:///mymusic.db', echo=True) # create a Session Session = sessionmaker(bind=engine) session = Session() # how to do a SELECT * (i.e. all) res = session.query(Artist).all() for artist in res: print(artist.name) # how to SELECT the first result res = session.query(Artist).filter(Artist.name=="Newsboys").first() # how to sort the results (ORDER_BY) res = session.query(Album).order_by(Album.title).all() for album in res: print(album.title) # how to do a JOINed query qry = session.query(Artist, Album) qry = qry.filter(Artist.id==Album.artist_id) artist, album = qry.filter(Album.title=="Step Up to the Microphone").first() # how to use LIKE in a query res = session.query(Album).filter(Album.publisher.like("S%a%")).all() for item in res: print(item.publisher)
评论
发表评论