python入门练习之如何连接数据库

!/usr/bin/python

-- coding: UTF-8 --

author = 'luke'
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

import traceback

打开数据库连接

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

Base = declarative_base() # 生成ORM基类
##################User Class##################################
class User(Base):
tablename = "hello_word" # 表名
id = Column(String(255), primary_key=True)
score = Column(Integer)
sccess_times = Column(Integer)
fail_times = Column(Integer)
http_type = Column(String(255))
##################IpRate Class##################################
class IpRate(Base):
tablename = "ip_rate" # 表名
id = Column(String(255), primary_key=True)
fail_times = Column(Integer)
score = Column(Integer)
sccess_times = Column(Integer)
http_type = Column(String(255))

def _addOne(obj):
try:

    session = session_class()
    #新增一条数据
    #原生sql：insert  into mysql.hello_word(name,password) values("test2","1234")；
    #obj = User(id="127.0.0.1:8080", score=5,sccess_times=0,fail_times=0,http_type='http')  #生成你要创建的数据对象
    session.add(obj) #把要创建的数据对象添加到这个session里， 一会统一创建
    session.commit() #统一提交，创建数据，在此之前数据库是不会有新增数据的
    print('addOne ok...')
except:
    session.rollback()
    print ("Error: unable to addOne data")
finally:
    session.close()

def _addAll(objlist):
'''
objlist: object list
'''
try:
session = session_class()
session.add_all(objlist) #把要创建的数据对象添加到这个session里， 一会统一创建
session.commit() #统一提交，创建数据，在此之前数据库是不会有新增数据的
except:
session.rollback()
print ("Error: unable to addAll data")
finally:
session.close()

def _del(obj):
'''
objlist: object list
'''
try:
session = session_class()
session.query(obj).delete() #通过session查询User类，然后过滤出id>5的进行删除
print ("_del...ok")
session.commit()
except:
session.rollback()
print ("Error: unable to delete data")
finally:
session.close()

def _updateOneById(obj,id,key,value):
try:
session = session_class()
data = session.query(obj).filter(id==id)
setattr(object=data,name=key,value=value)
session.commit()
print ("_updateOneById...ok")
except:
session.rollback()
print ("Error: unable to update data")
finally:
session.close()

def _fetchOneById(obj,id):
try:
session = session_class()
results = session.query(obj).filter(obj.idid)
if(len(results) > 0):
print ("_fetchOneById...ok")
return results[0]
else:
return None
except:
print ("Error: unable to _fetchOneById data")
finally:
session.close()
def _fetchAll(obj):
try:
session = session_class()
results = session.query(obj).all() #查询所有
if(len(results) > 0):
print ("_fetchAll...ok")
return results
else:
return None
except:
print ("Error: unable to _fetchAll data")
finally:
session.close()
if name'main':
try:
#Base.metadata.create_all(connect) # 创建表结构
#print('创建成功')
user = User(id='127.0.0.2:8080',score=10,sccess_times=0,fail_times=0,http_type='http')
# user.id = '127.0.0.1:8080'
# user.score = 10
# user.sccess_times = 0
# user.fail_times = 0
# user.http_type = 'http'
#_addOne(user)
data = _fetchOneById(User,'127.0.0.1:8080')
print(data)
data2 = _fetchAll(User)
_updateOneById(User,'127.0.0.1:8080','score',1)
_del(user)
except BaseException as e:
traceback.print_exc()