1. 创建表
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @date: 2018/5/30 上午8:32
# @author: Bill
# @file: create_tb.py
from sqlalchemy import create_engine, MetaData, Table, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class CreateTable(object):
def getEngine(self):
self.engine = create_engine("mysql+pymysql://alchemy:*****@fishxq.gicp.net:***/sqlalchemy?charset=utf8") # ?charset=utf8要加上
return self.engine
def createTb(self):
# 创建一个表用于存储dictionary.txt中的数据
metadata = MetaData(self.getEngine())
dictionary_tbconfig = Table("dictionary", metadata,
Column('id', Integer, primary_key=True),
Column('key', String(50)),
Column('value', String(50))
)
metadata.create_all(self.getEngine())
create_table = CreateTable()
create_table.createTb()
2. 将字典文件dictionary.txt中的内容写入数据库
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @date: 2018/5/30 上午8:58
# @author: Bill
# @file: write_to_db.py
import codecs
from sqlalchemy.orm import sessionmaker
from dictionary.create_tb import *
class Dictionary(Base): # 此处的表结构要和建表是的结构一样,不然会出错
__tablename__ = 'dictionary'
id = Column(Integer,primary_key=True)
key = Column(String(50))
value = Column(String(50))
class WriteToDB(object):
def __init__(self):
engine = CreateTable().getEngine()
DBsessoin = sessionmaker(bind=engine)
self.session = DBsessoin()
def writeData(self,filename):
with codecs.open(filename,'r',encoding="utf-8") as f:
for num, content in enumerate(f.readlines()):
key = content.strip().split()[0]
value = content.strip().split()[1]
dictionary = Dictionary(id=num+1,key=key,value=value)
self.session.add(dictionary)
self.session.commit()
self.session.close()
# writetodb = WriteToDB() # 写过一次了 不用打开注释
# writetodb.writeData('dictionary.txt')
3. 实现查询功能
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @date: 2018/5/30 上午10:25
# @author: Bill
# @file: search_word.py.py
from sqlalchemy.orm import sessionmaker
from dictionary.create_tb import *
from dictionary.write_to_db import Dictionary
engine = CreateTable().getEngine()
DBsessoin = sessionmaker(bind=engine)
session = DBsessoin()
word = input("Please input a word you want to search: ")
result = session.query(Dictionary).filter(Dictionary.key == f'{word}').all()
for res in result:
print(res.value)
4. 数据库的几个坑
默认建表的字符集一直是latin-1,解决方法是
1. 删除database
2. 修改my.cnf, 加入character-set-server = utf8
3. 重启mysql
4. 在创建库和表,这样默认的字符集就是utf8了