3.3.5-ORM框架【多选一:Flask系列】
第1关:使用Flask进行数据库开发
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import datetime
import warnings
warnings.filterwarnings("ignore")
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123123@127.0.0.1:3306/flask_table'
db = SQLAlchemy(app)
class entry_Form(db.Model):
__tablename__ = 'entry_form'
ID = db.Column(db.Integer, primary_key=True, autoincrement=True)
company_name = db.Column(db.String(255), nullable=False)
eduLevel_name = db.Column(db.String(255))
Entry_time = db.Column(db.Date, default=datetime.date(2019, 1, 1))
jobName = db.Column(db.String(255))
salary = db.Column(db.Integer)
def createTable(self):
db.drop_all()
db.create_all()
if __name__ == '__main__':
with app.app_context():
entry_form_instance = entry_Form()
entry_form_instance.createTable()
print("您创建的表格式正确!")
第2关:查询操作
from task import entry_Form
import datetime
class Test():
def select_Table(self):
results = entry_Form.query.filter(
entry_Form.company_name == '阿里',
entry_Form.eduLevel_name == '硕士',
entry_Form.Entry_time.between(datetime.date(2019, 6, 1), datetime.date(2019, 6, 30)),
entry_Form.salary.between(20000, 25000)
).all()
output = []
for result in results:
output.append([
result.ID,
result.company_name,
result.eduLevel_name,
result.Entry_time.strftime('%Y-%m-%d'),
result.jobName,
result.salary
])
return output
if __name__ == '__main__':
test_instance = Test()
data = test_instance.select_Table()
for i in data:
print(i)
第3关:添加操作
import pandas as pd
from task import db, entry_Form
import datetime
class Message:
def update_table(self):
df = pd.read_csv('data.csv', delimiter='\t')
entries = []
for index, row in df.iterrows():
entry = entry_Form(
ID=row['ID'],
company_name=row['company_name'],
eduLevel_name=row['eduLevel_name'],
Entry_time=datetime.datetime.strptime(row['Entry_time'], '%Y-%m-%d').date(),
jobName=row['jobName'],
salary=row['salary']
)
entries.append(entry)
db.session.add_all(entries)
db.session.commit()
第4关:删除操作
from task import db, entry_Form
class Demo:
def del_col(self):
db.session.query(entry_Form).filter(
~((entry_Form.jobName.in_(['Java工程师', 'Python工程师'])) & (entry_Form.company_name != '华为'))
).delete(synchronize_session=False)
db.session.commit()
第5关:修改操作
from task import db, entry_Form
class Demo:
def update_Date(self):
user = entry_Form.query.filter_by(ID=10).first()
user.salary = 30000
db.session.commit()