字典查询功能实现过程

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了

转载于:https://my.oschina.net/u/3822958/blog/1821029