Python Data Analysis Learning notes eight

Eighth Chapter application Database

1 lightweight access based on Sqlite3

A lightweight relational database

The sample code is as follows:

ImportSqlite3 # Create a database Join withSqlite3.connect (": Memory:") asCon:     get cursor     c = con.cursor ()     # CREATE database table     c.execut E (' '         CREATE TABLE Sensors (                              data text,                              City text,                               code text,                              Sensor_ ID Real,                  & NBsp;          temperature Real) # Query database table
  forTable inC.execute ("Select name from Sqlite_master WHERE type= ' table"): Print (' table ', table) # Add Records C.execute ("INSERT into S
Ensors VALUES (' 2016-02-27 ', ' UTRECHT ', ' Red ', 42,15.14) ') # query all Records C.execute ("SELECT * from sensors") print (C.fetchone ()) # Delete Table Con.execute (' DROP table sensors ') # Query database table print ("#of table", C.execute ("Select COUNT (*) from Sqlite_master WHERE t Ype= ' table '). Fetchone () [0]) # Close join Con.close ()

The results of the operation are as follows:

Table (' sensors ',)

(' 2016-02-27 ', ' UTRECHT ', ' Red ', 42.0,15.14)

#of Table 0

2 Accessing the database via Pandas

The sample code is as follows:

ImportStatsmodels.api asSm fromPandas.io.sqlImportRead_sqlImportSqlite3 # Create a database Join withSqlite3.connect (": Memory:") asCon: # get cursor c = con.cursor () # load Data Data_loader = Sm.datasets.sunspots.load_pandas () df = Data_loader . Data # Create a tuple list rows = [Tuple (x) forX inDf.values] # Create a datasheet with unspecified data types Con.execute ("CREATE table Sunspots (year,sunactivity)") # Add more than one record Con.executem Any (INSERT into sunspots (year,sunactivity) VALUES (?,?), rows) # Query The total number of records C.execute ("Select COUNT (*)" from sunspots ") Print (C.fetchone ()) # Deletes the records in the table and displays the number of affected rows print (" Deleted ", Con.execute (" Delete from sunspots where sunactivity >20 "). RowCount," row "# Use Read_sql to execute query and return dataframe result print (Read_sql (" select * from sunspots where year <17 # ", con") # Delete datasheet Con.execute ("DROP table Sunspots") C.close ()

The results of the operation are as follows:

(309,)

Deleted 217 Row

Year Sunactivity

0 1700.0 5.0

1 1701.0 11.0

2 1702.0 16.0

3 1707.0 20.0

4 1708.0 10.0

5 1709.0 8.0

6 1710.0 3.0

7 1711.0 0.0

8 1712.0 0.0

9 1713.0 2.0

10 1714.0 11.0

11 1723.0 11.0

3 SQLAlchemy

You can map classes in Python to tables in the database, classes in Java Hibernate

The sample code is as follows:

alchemy_entities.py

 fromSQLAlchemyImportColumn, ForeignKey, Integer, Float, String fromSqlalchemy.ext.declarativeImportDeclarative_base fromSqlalchemy.ormImportRelationship fromSQLAlchemyImportCreate_engine fromSQLAlchemyImportUniqueConstraint # super Class Base = Declarative_base () # Observatory tableclass Station(Base): __tablename__ = ' Station ' # table name id = Column (Integer, primary_key=True# id name = Column (String), nullable=False, unique=True) # Observatory Namedef__repr__ (self): return"Id=%d name=%s"% (Self.id, self.name) # sensor tableclass Sensor(Base): __tablename__ = ' sensor ' # table name id = Column (Integer, primary_key=True# ID last = column (integer) multiplier = column (Float) station_id = Column (Integer, ForeignKey (' station.id ') ) # foreign Key station = Relationship (station)def__repr__ (self): return"Id=%d last=%d multiplier=%.1f station_id=%d" (Self.id, Self.last, Self.multiplier, self.station_id)if__name__ = = "__main__": Print ("This script was used by another script.") Run python alchemy_query.py ")

populate_db.py

 fromSQLAlchemyImportCreate_engine fromSqlalchemy.ormImportSessionmaker from. alchemy_entitiesImportBase, Sensor, stationdef populate(engine): # Create dbsession object Base.metadata.bind = Engine Dbsession = Sessionmaker (bind=engine) session = Dbse Ssion () # Create two observatories de_bilt = station (name= ' de bilt ') session.add (de_bilt) Session.add (Station (name= ' Utrec HT ') Session.commit () print (' Station ', de_bilt) # add sensor Records temp_sesor = Sensor (last=20, multiplier=.1, St ation=de_bilt) Session.add (temp_sesor) session.commit () print ("Sensor", Temp_sesor)if__name__ = = "__main__": Print ("This script was used by another script.") Run python alchemy_query.py ")

alchemy_query.py

 fromEight.alchemy_entitiesImportBase, Station, Sensor fromeight.populate_dbImportPopulate fromSQLAlchemyImportCreate_engine fromSqlalchemy.ormImportSessionmakerImportOs fromPandas.io.sqlImportRead_sql # Create Engine engine = Create_engine (' sqlite:///demo.db ') # Delete datasheet Base.metadata.drop_all (engine) # Create data table Base.metadata. Create_all (engine) populate (engine) Base.metadata.bind = engine Dbsession = Sessionmaker () Dbsession.bind = Engine Sessio n = dbsession () # Query the first row of records in the station table station = Session.query (station). All station print ("All Station", session. Query (station). All ()) # Queries all sensor print (' All sensor ', session.query (sensor). All ()) # Query the first station print (' Query sensor by station ', session.query (sensor). Filter (sensor.station = station). One ()) # using Pandas read_sql query print (' Read_sql all Station ', Read_sql ("SELECT * from Station", Engine.raw_connection ()) # Delete database, not executedTry: Os.remove (' demo.db ') print (' Delete demo.db ')exceptOSError asE: # [Winerror 32] Another program is using this file and the process is inaccessible. : ' demo.db ' Print (e) Pass

The results of the operation are as follows:

Station Id=1 Name=de Bilt

Sensor id=1 last=20 multiplier=0.1station_id=1

All station [id=1 name=de Bilt, Id=2name=utrecht]

All sensor [id=1 last=20 multiplier=0.1station_id=1]

Query sensor by station Id=1 last=20multiplier=0.1 station_id=1

Read_sql All station ID name

0 1 De bilt

1 2 Utrecht

[Winerror 32] Another program is using this file and the process is inaccessible. : ' Demo.db '

4 Pony ORM

The ORM package written in Python

Database, db_session

to_sqlsm

# Create SQLite
db = Databases (' SQLite ', ': Memory: ')

# Load data and write to
 database db_session:
data_loader = Sm.datasets.sunspots.load_ Pandas ()
df = data_loader.data
to_sql (DF, Sunspots, db.get_connection ())
print (Db.select ("Cou NT (*) from sunspots "))

The results of the operation are as follows:

[309]

5 Dataset Lazy Man Database

It's a sqlalchemy wrapper.

  Import  DataSet  from  pandas.io.sql  import  read_sql  From  pandas.io.sql  import  to_sql  import  Statsmodels.api  as  SM # CREATE DATABASE Connection db = Dataset.connect (' sqlite:///:memory: ') # Create books Table tables = db["Books"] #