The use of sqlalchemy framework in python does not need to write SQL insert statements that are too long to be disgusting. pythonsqlalchemy
Import time
# Create an instance and connect to the database
From sqlalchemy import create_engine
From sqlalchemy import Column
From sqlalchemy import Integer
From sqlalchemy import String
From sqlalchemy. ext. declarative import declarative_base
From sqlalchemy. orm import sessionmaker
Engine = create_engine ('mysql + pymysql: // root: qwe123@192.168.59.111/tao_bao? Charset = utf8 ',
Encoding = 'utf-8', echo = True) # // User name: password @ ip/Database Name
# Create a session class with the database. Note that the class returned to the session is not an instance.
Session_class = sessionmaker (bind = engine) # bind the instance to the engine
Session = session_class () # generate a session instance, which is equivalent to a cursor
Base = declarative_base () # generate an orm Base class
Class parseHayao_liuliang (Base ):
_ Tablename _ = "history_liuliang" # name of the created table
Id = Column (Integer, primary_key = True) # auto-Increment
# Store name
Shop_name = Column (String (25 ))
# Query the historical data date
History_date = Column (String (25 ))
# Visitor count
Uv = Column (String (25 ))
# Page views
Pv = Column (String (25 ))
# Hop Loss Rate
BounceRate = Column (String (25 ))
# Views per user
AvgPv = Column (String (25 ))
# Average stay duration
StayTime = Column (String (25 ))
# Old visitor count
OldUv = Column (String (25 ))
# New visitor count
NewUv = Column (String (25 ))
# Number of followers
ShopCltByrCnt = Column (String (25 ))
# Current time
Date = Column (String (25 ))
Def _ init _ (self ):
# Create a table structure (Child class of the parent class)
Base. metadata. create_all (engine)
# Save to mysql database
Def insert (self ):
Try:
Session. add (self) # add the data object to be created to this session.
Session. commit () # submit and create data only now
Failed t Exception as e:
Print (e)
Session. rollback ()
Def to_mysql (self, item ):
Data = item
Self. shop_name = 'official flagship store of Hayao'
Self. history_date = data ['query date']
Self. uv = data ['visitor number']
Self. pv = data ['page views ']
Self. bounceRate = data ['hop loss rate']
Self. avgPv = data ['per user view']
Self. stayTime = data ['average residence time']
Self. oldUv = data ['old visitor number']
Self. newUv = data ['new visitor number']
Self. shopCltByrCnt = data ['focus on the number of shops']
Self. date = time. strftime ('% Y-% m-% d % H: % M: % s ')
Self. insert ()