python pymysql mysql乱码记事

taochifan

已于 2023-12-05 18:25:09 修改

阅读量1.1k

点赞数

分类专栏： python2 文章标签： python pymysql mysql

于 2017-05-19 09:52:41 首次发布

本文链接：https://blog.csdn.net/gd4_txw/article/details/72518123

版权

python2 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

#-*-coding:utf-8-*-
'''
import pymysql.cursors
#connect to database
connection=pymysql.connect(host='',
                           user='',
                           password='',
                           db='',
                           cursorclass=pymysql.cursors.DictCursor)

cursors.query(show databases();)
cursors.close()
fisrt fail
'''
from __future__ import print_function
import pymysql
conn = pymysql.connect(host='',port=3306,user='',passwd='',db='')
cur = conn.cursor()
cur.execute("set names 'UTF8'")
#raw_input_a = raw_input(unicode("in put query_name:").decode("gbk")) 这里不用转成unicode，然后再解码gbk
# valueerror:unsupported format character '''(0x27)
raw_input_a = raw_input("in put query_name: ")
#sql = "SELECT Par_Label from partern where Par_Parent = 1961 order by Par_sort asc"
#下面的sql语句中like语句中的前后的%要进行转义%%，即%%%s%%,% (raw_input_a)然后因为查询中文还需要添加binary，即like binary %%%s%%
sql = "SELECT p.per_name,p.Per_Sort,p.Per_ID,d.Par_ID,d.par_label FROM `personnel` as p left JOIN `per_par` as pd on p.per_id = pd.Per_ID LEFT JOIN `partern` as d on pd.par_ID = d.Par_ID where p.Per_Name like binary '%%%s%%'" % (raw_input_a)
cur.execute(sql)
description = cur.description
if description:
    for dd in description:
        print (dd[0].decode("utf-8"),end=", ") #这里end""是print的参数，就是不换行，输出到同一行
#上面因为cur.description各个项有很多参数，用for循环取第一个参数[0]        
print()
print("姓名, 排序, 用户ID, 部门ID, 所属部门")
alldata = cur.fetchall()
if alldata:
    for row in alldata:
        print (row[0].decode("utf-8"),row[1],row[2],row[3],row[4].decode("utf-8"))
#        print (row)   如果没有decode("utf-8")的话输出的是unicode字符'\xe6\xb1\x9f...'
#如果没有[0]，就会提示tuple objectg has no attribute decode.
# print("姓名, 排序, 用户ID, 部门ID") 这里会乱码，为什么？
def display_rank():
    raw_input_B = int(raw_input("in put par_ID: "))
    sql1 = "SELECT p.per_name,p.Per_Sort,p.Per_ID,pd.Par_ID FROM `personnel` as p left JOIN `per_par` as pd on p.per_id = pd.Per_ID where pd.par_ID = %d order by p.Per_Sort asc" % (raw_input_B)
    cur.execute(sql1)
    alldata1 = cur.fetchall()
    if alldata1:
        for row in alldata1:
            print (row[0].decode("utf-8"),row[1],row[2],row[3])
display_rank()
raw_input_c = int(raw_input("in put rank_ID: "))
raw_input_d = int(raw_input("in put per_ID: "))
sql2 = "update `personnel` set per_sort = %d where per_id = %d" % (raw_input_c,raw_input_d)
cur.execute(sql2)
display_rank()
cur.close()
conn.close()

#怎么让python暂停，分段运行？ raw_input可以实现,然后格式化输入
#raw_input_B = int(raw_inpu("in put ID: "))
#%d % (raw_input_B)

<a data-cke-saved-href="http://pank.org/blog/2013/04/mysql-illegal-mix-of-collation.html" href="http://pank.org/blog/2013/04/mysql-illegal-mix-of-collation.html">binary like</a>

<a data-cke-saved-href="http://blog.csdn.net/zm2714/article/details/7974890" href="http://blog.csdn.net/zm2714/article/details/7974890">python 编码</a>

后来又修改了一些汉字输入方面的内容

# -*- coding: GB2312-*- #

'''
function：query and modify the rank of staffs.
version:2 maybe the last version
'''
from __future__ import print_function
import pymysql
conn = pymysql.connect(host='',port=3306 ,user='',passwd='',db='')
cur = conn.cursor()
cur.execute("set names 'UTF8'")

def check_department():
    '''
判断输入的中文字符的长度，添加空格
一个汉字占用两个字符，所以两个汉字的长度是4.:)
如下判断len()函数得到的字符长度因符编码不同而不同而非占用的字节数
>>> test = u'杭州'
>>> test
u'\u676d\u5dde'
>>> len (test)
2
>>> help(len)
Help on built-in function len in module __builtin__:

len(...)
    len(object) -> integer

    Return the number of items of a sequence or collection.

>>> type(test)
<type 'unicode'>
>>> test.encode('utf-8')
'\xe6\x9d\xad\xe5\xb7\x9e'
>>> len(test.encode('utf-8'))
6
>>> test.encode('gb2312')
'\xba\xbc\xd6\xdd'
>>> len(test.encode('gb2312'))
4

slice切片[:2]，后面的2不包含！！基础不扎实啊。
    '''
    raw_input_aa= raw_input(u"input query_name: 输入要查询的名字\n")
    if len(raw_input_aa) == 4 :
        raw_input_a = raw_input_aa[:2] +" "*3 +raw_input_aa[2:]
    else:
        raw_input_a = raw_input_aa
    sql = "SELECT p.per_name,p.Per_Sort,p.Per_ID,d.Par_ID,d.par_label FROM `personnel` as p left JOIN `per_par` as pd on p.per_id = pd.Per_ID LEFT JOIN `partern` as d on pd.par_ID = d.Par_ID where p.Per_Name like binary '%%%s%%'" % (raw_input_a)
    #上面的sql语句中like语句中的前后的%要进行转义%%，即%%%s%%,% (raw_input_a)然后因为查询中文还需要添加binary，即like binary %%%s%%
    cur.execute(sql)
    description = cur.description
    if description:
        for dd in description:
            print (dd[0].decode("utf-8"),end=", ") #这里end""是print的参数，就是不换行，输出到同一行
                #上面因为cur.description各个项有很多参数，用for循环取第一个参数[0]        
    print()
    print("姓名, 排序, 用户ID, 部门ID, 所属部门")
    alldata = cur.fetchall()
    if alldata:
        for row in alldata:
            print (row[0].decode("utf-8"),row[1],row[2],row[3],row[4].decode("utf-8"))
def display_rank():
    '''查询部门中排序'''
    raw_input_B = int(raw_input(u"input par_ID: "))
    sql1 = "SELECT p.per_name,p.Per_Sort,p.Per_ID FROM `personnel` as p left JOIN `per_par` as pd on p.per_id = pd.Per_ID where pd.par_ID = %d order by p.Per_Sort asc" % (raw_input_B)
    cur.execute(sql1)
    description = cur.description
    if description:
        for dd in description:
            print (dd[0],end=",")
##    字符串前面添加u...这个是不对字符串进行什么处理?字符编码还是一个问题。
    print()
    print("姓名, 排序, 用户ID")
    alldata1 = cur.fetchall()
    if alldata1:
        for row in alldata1:
            print (row[0].decode("utf-8"),row[1],row[2])
##            print (u"row[0],row[1],row[2]")

def modify_rank():
    '''修改排序'''
    raw_input_c = int(raw_input(u"input rank_id,i.e.Per_Sort: "))
    raw_input_d = int(raw_input(u"input per_ID: "))
    sql2 = "update `personnel` set per_sort = %d where per_id = %d" % (raw_input_c,raw_input_d)
    cur.execute(sql2)
    print ("Rank successfully modified.Please flash browser to check it.")
    
## the main .
while True:
    raw_input_e= raw_input(u"What do you want to do? Check_department or Display_rank or Modify_rank or Quit? \n Press the initials (the first letter)above.")

    if raw_input_e == 'c' or raw_input_e == 'C':
        check_department()
    elif raw_input_e == 'd' or raw_input_e == 'D':
        display_rank()
    elif raw_input_e == 'm' or raw_input_e == 'M':
        modify_rank()
    elif raw_input_e == 'q' or raw_input_e == 'Q':
        break       
cur.close()
conn.close()