python的mysql练习题

最新推荐文章于 2022-12-28 11:58:32 发布

原创最新推荐文章于 2022-12-28 11:58:32 发布 · 512 阅读

0 ·

CC 4.0 BY-SA版权

python 专栏收录该内容

41 篇文章

订阅专栏

本文记录了使用Python的sqlalchemy库将txt文件内容写入MySQL数据库的过程。在实践中遇到字符集问题，通过调整解决了无法插入数据的难题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

把txt文件中的内容写入sql

今天练习了使用sqlalchemy把txt文件中的内容写入数据库。遇到了很多问题，通过百度解决了问题，主要还是不熟练，缺少练习。

代码例子

#! /usr/bin/env python
# -*- coding: utf-8 -*-
"""
-------------------------------------------------
 Time      ：2018/05/30  22:03
 Author    : shanwu
 File Name ：createtable.py
-------------------------------------------------
"""

import codecs
import pymysql

from sqlalchemy import Integer, Column, String, create_engine, MetaData,Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://root:123456@192.168.238.144:3306/testdb?charset=utf8')

Base = declarative_base()
class Dictionary(Base):
    __tablename__ = 'dictionary'
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8mb4'
    }
    id = Column(Integer, primary_key=True)
    key = Column(String(50))
    value = Column(String(50))

Base.metadata.create_all(engine)

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()

需要注意的点是创建出来的表字符集不是utf8，导致出现了无法插入数据。执行下面命令就可以了。

alter table testdb.dictionary convert to character set utf8;

查询表的内容的代码

#! /usr/bin/env python
# -*- coding: utf-8 -*-
"""
-------------------------------------------------
 Time      ：2018/05/30  23:08
 Author    : shanwu
 File Name ：2308.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://root:123456@192.168.238.144:3306/testdb?charset=utf8')

Base = declarative_base()
class Dictionary(Base):
    __tablename__ = 'dictionary'
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8mb4'
    }
    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)

#输出结果
please input your a word:yacht
7956 yacht n.游船，快艇