Python学习_26 python练习



Python学习_26 python练习

python练习

需求：把一个英文单词表插入数据库，然后可以查询

1、创建表

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Time    : 2018\5\31 0031 1:13

# @Author  : xiexiaolong

# @File    : createtable.py

import codecs

from sqlalchemy import Column, MetaData, Table

from sqlalchemy import Integer

from sqlalchemy import String

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+ pymysql://yanzi:yanzi1097@193.112.92.65/yanzi ')

metadata = MetaData(engine)

dictionary = Table('dictionary', metadata,

             Column('id', Integer, primary_key=True),

             Column('key', String(50)),

             Column('value', String(50))

             )

metadata.create_all(engine)

2、插入数据库

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Time    : 2018\5\31 0031 1:32

# @Author  : xiexiaolong

# @File    : insertdatas.py

import codecs

from sqlalchemy import Integer, Column, String, create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+ pymysql://yanzi:yanzi1097@193.112.92.65/yanzi?charset=utf8 ')

Base = declarative_base()

class Dictionary(Base):

    __tablename__ = 'dictionary'

    id = Column(Integer, primary_key=True)

    key = Column(String(50))

    value = Column(String(50))

DBSession = sessionmaker(bind=engine)

session = DBSession()

class HandleData(object):

    def __init__(self, dataFile):

        self.dataFile = dataFile

    def make_data_to_str(self):

        with codecs.open(self.dataFile, encoding='utf-8') as file:

            for (num, value) in enumerate(file):

                line = value.strip().split()

                diction = Dictionary(id=num + 1, key=line[0], value=line[1])

                session.add(diction)

session.commit ()

handleData = HandleData('dictionary.txt')

handleData.make_data_to_str()

session.close()

3、查询

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @Time    : 2018\5\31 0031 1:34

# @Author  : xiexiaolong

# @File    : selectData.py

from sqlalchemy import create_engine, Integer, Column, String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+ pymysql://yanzi:yanzi1097@193.112.92.65/yanzi?charset=utf8 ')

Base = declarative_base()

class Dictionary(Base):

    __tablename__ = 'dictionary'

    id = Column(Integer, primary_key=True)

    key = Column(String(50))

    value = Column(String(50))

DBSession = sessionmaker(bind=engine)

session = DBSession()

word = input("please input your a word:")

result =session.query(Dictionary).filter(Dictionary.key.like("%{0}%".format(word))).all()

for each in result:

    print(each.id, each.key, each.value)