python开发小技巧

今天在工作中写了一个python脚本从数据库中导数据,其中用到了一些技巧,在这里记录一下。

判断字符串仅包含英文

直接通过字符的ord来判断

def is_pure_english(check_str):
  return all(ord(c) < 128 for c in check_str)

判断字符串中包含某些语言的字符

根据字符的unicode范围判断是否包含某些语言的字符

def contains_invalid_lang_chs(check_str):
  check_str=check_str.strip()
  # 判断包含任何阿拉伯文、朝鲜文、日文平假名、日文片假名、日文片假名语音扩展、朝鲜文音节、俄文(西里尔字母、西里尔字母补充)
  return any((u'\u0600' <= c <= u'\u06FF') or (u'\u1100' <= c <= u'\u11FF') or (u'\u3040' <= c <= u'\u309F') or (u'\u30A0' <= c <= u'\u30FF') or (u'\u31F0' <= c <= u'\u31FF') or (u'\uAC00' <= c <= u'\uD7AF') or (u'\u0400' <= c <= u'\u052F') for c in check_str)

完整的UNICODE编码表如下:

十进制 Unicode 编码十六进制 Unicode 编码字符数编码分类(中文)编码分类(英文)
起始终止起始终止(个)
01270007F128C0控制符及基本拉丁文C0 Control and Basic Latin
1282558000FF128C1控制符及拉丁文补充-1C1 Control and Latin 1 Supplement
256383100017F128拉丁文扩展-ALatin Extended-A
384591180024F208拉丁文扩展-BLatin Extended-B
59268725002AF96国际音标扩展IPA Extensions
68876702B002FF80空白修饰字母Spacing Modifiers
768879300036F112结合用读音符号Combining Diacritics Marks
880102337003FF144希腊文及科普特文Greek and Coptic
1024127940004FF256西里尔字母Cyrillic
12801327500052F48西里尔字母补充Cyrillic Supplement
13281423530058F96亚美尼亚语Armenian
1424153559005FF112希伯来文Hebrew
1536179160006FF256阿拉伯文Arabic
17921871700074F80叙利亚文Syriac
18721919750077F48阿拉伯文补充Arabic Supplement
1920198378007BF64马尔代夫语Thaana
1984204707C007FF64西非書面語言N’Ko
20482143800085F96阿维斯塔语及巴列维语Avestan and Pahlavi
21442175860087F32MandaicMandaic
2176222388008AF48撒马利亚语Samaritan
23042431900097F128天城文书Devanagari
2432255998009FF128孟加拉语Bengali
256026870A000A7F128锡克教文Gurmukhi
268828150A800AFF128古吉拉特文Gujarati
281629430B000B7F128奥里亚文Oriya
294430710B800BFF128泰米尔文Tamil
307231990C000C7F128泰卢固文Telugu
320033270C800CFF128卡纳达文Kannada
332834550D000D7F128德拉维族语Malayalam
345635830D800DFF128僧伽罗语Sinhala
358437110E+000E7F128泰文Thai
371238390E+000EFF128老挝文Lao
384040950F000FFF256藏文Tibetan
409642551000109F160缅甸语Myanmar
4256435110A010FF96格鲁吉亚语Georgian
43524607110011FF256朝鲜文Hangul Jamo
460849911200137F384埃塞俄比亚语Ethiopic
499250231380139F32埃塞俄比亚语补充Ethiopic Supplement
5024511913A013FF96切罗基语Cherokee
512057591400167F640统一加拿大土著语音节Unified Canadian Aboriginal Syllabics
576057911680169F32欧甘字母Ogham
5792588716A016FF96如尼文Runic
588859191700171F32塔加拉语Tagalog
592059511720173F32HanunóoHanunóo
595259831740175F32BuhidBuhid
598460151760177F32TagbanwaTagbanwa
60166143178017FF128高棉语Khmer
61446319180018AF176蒙古文Mongolian
6320639918B018FF80ChamCham
640064791900194F80LimbuLimbu
648065271950197F48德宏泰语Tai Le
65286623198019DF96新傣仂语New Tai Lue
662466551.9E+0119FF32高棉语记号Kmer Symbols
665666871A001A1F32BugineseBuginese
668867511A201A5F64BatakBatak
678468951A801AEF112LannaLanna
691270391B001B7F128巴厘语Balinese
704070881B801BB049巽他语Sundanese
710471671BC01BFF64Pahawh HmongPahawh Hmong
716872471C001C4F80雷布查语Lepcha
724872951C501C7F48Ol ChikiOl Chiki
729673911C801CDF96曼尼普尔语Meithei/Manipuri
742475511D001D7F128语音学扩展Phonetic Extensions
755276151D801DBF64语音学扩展补充Phonetic Extensions Supplement
761676791DC01DFF64结合用读音符号补充Combining Diacritics Marks Supplement
768079351E+001EFF256拉丁文扩充附加Latin Extended Additional
793681911F001FFF256希腊语扩充Greek Extended
819283032000206F112常用标点General Punctuation
830483512070209F48上标及下标Superscripts and Subscripts
8352839920A020CF48货币符号Currency Symbols
8400844720D020FF48组合用记号Combining Diacritics Marks for Symbols
844885272100214F80字母式符号Letterlike Symbols
852885912150218F64数字形式Number Form
85928703219021FF112箭头Arrows
87048959220022FF256数学运算符Mathematical Operator
89609215230023FF256杂项工业符号Miscellaneous Technical
921692792400243F64控制图片Control Pictures
928093112440245F32光学识别符Optical Character Recognition
93129471246024FF160封闭式字母数字Enclosed Alphanumerics
947295992500257F128制表符Box Drawing
960096312580259F32方块元素Block Element
9632972725A025FF96几何图形Geometric Shapes
97289983260026FF256杂项符号Miscellaneous Symbols
998410175270027BF192印刷符号Dingbats
101761022327C027EF48杂项数学符号-AMiscellaneous Mathematical Symbols-A
102241023927F027FF16追加箭头-ASupplemental Arrows-A
1024010495280028FF256盲文点字模型Braille Patterns
10496106232900297F128追加箭头-BSupplemental Arrows-B
1062410751298029FF128杂项数学符号-BMiscellaneous Mathematical Symbols-B
10752110072A002AFF256追加数学运算符Supplemental Mathematical Operator
11008112632B002BFF256杂项符号和箭头Miscellaneous Symbols and Arrows
11264113592C002C5F96格拉哥里字母Glagolitic
11360113912C602C7F32拉丁文扩展-CLatin Extended-C
11392115192C802CFF128古埃及语Coptic
11520115672D002D2F48格鲁吉亚语补充Georgian Supplement
11568116472D302D7F80提非纳文Tifinagh
11648117432D802DDF96埃塞俄比亚语扩展Ethiopic Extended
11776119032E+002E7F128追加标点Supplemental Punctuation
11904120312E+802EFF128CJK 部首补充CJK Radicals Supplement
12032122552F002FDF224康熙字典部首Kangxi Radicals
12272122872FF02FFF16表意文字描述符Ideographic Description Characters
12288123513000303F64CJK 符号和标点CJK Symbols and Punctuation
12352124473040309F96日文平假名Hiragana
124481254330A030FF96日文片假名Katakana
12544125913100312F48注音字母Bopomofo
12592126873130318F96朝鲜文兼容字母Hangul Compatibility Jamo
12688127033190319F16象形字注释标志Kanbun
127041273531A031BF32注音字母扩展Bopomofo Extended
127361278331C031EF48CJK 笔画CJK Strokes
127841279931F031FF16日文片假名语音扩展Katakana Phonetic Extensions
1280013055320032FF256封闭式 CJK 文字和月份Enclosed CJK Letters and Months
1305613311330033FF256CJK 兼容CJK Compatibility
133121990334004DBF6592CJK 统一表意符号扩展 ACJK Unified Ideographs Extension A
19904199674DC04DFF64易经六十四卦符号Yijing Hexagrams Symbols
19968408954E+009FBF20928CJK 统一表意符号CJK Unified Ideographs
4096042127A000A48F1168彝文音节Yi Syllables
4212842191A490A4CF64彝文字根Yi Radicals
4224042527A500A61F288VaiVai
4259242751A660A6FF160统一加拿大土著语音节补充Unified Canadian Aboriginal Syllabics Supplement
4275242783A700A71F32声调修饰字母Modifier Tone Letters
4278443007A720A7FF224拉丁文扩展-DLatin Extended-D
4300843055A800A82F48Syloti NagriSyloti Nagri
4307243135A840A87F64八思巴字Phags-pa
4313643231A880A8DF96SaurashtraSaurashtra
4326443391A900A97F128爪哇语Javanese
4339243487A980A9DF96ChakmaChakma
4352043583AA00AA3F64Varang KshitiVarang Kshiti
4358443631AA40AA6F48Sorang SompengSorang Sompeng
4364843743AA80AADF96NewariNewari
4377643871AB00AB5F96越南傣语Vi?t Thái
4390443936AB80ABA033Kayah LiKayah Li
4403255215AC00D7AF11184朝鲜文音节Hangul Syllables
5529656319D800DBFF1024High-half zone of UTF-16High-half zone of UTF-16
5632057343DC00DFFF1024Low-half zone of UTF-16Low-half zone of UTF-16
5734463743E000F8FF6400自行使用區域Private Use Zone
6374464255F900FAFF512CJK 兼容象形文字CJK Compatibility Ideographs
6425664335FB00FB4F80字母表達形式Alphabetic Presentation Form
6433665023FB50FDFF688阿拉伯表達形式AArabic Presentation Form-A
6502465039FE00FE0F16变量选择符Variation Selector
6504065055FE10FE1F16竖排形式Vertical Forms
6505665071FE20FE2F16组合用半符号Combining Half Marks
6507265103FE30FE4F32CJK 兼容形式CJK Compatibility Forms
6510465135FE50FE6F32小型变体形式Small Form Variants
6513665279FE70FEFF144阿拉伯表達形式BArabic Presentation Form-B
6528065519FF00FFEF240半型及全型形式Halfwidth and Fullwidth Form
6552065535FFF0FFFF16特殊Specials

执行SQL语句

创建一个生成器方法用于执行SQL语句

def sql_query(sql):
  try:
    conn = pymysql.connect(host=DB_IP, port=DB_PORT, user=DB_USER, password=DB_PASSWD, \
                          db=DB_NAME, charset='utf-8', cursorclass=pymysql.cursors.DictCursor)
    with conn.cursor() as cursor:
      cursor.execute(sql)
      for row in cursor:
        yield row
  finally:
    conn.close()
 
results=sql_query('select * from users')
for row in results:
  print(row['id'])

将python依赖库打包一起分发

有时执行脚本的服务器,没有足够的权限,无法通过pip安装python依赖库,这时可以将依赖库打包起来随同脚本一起分发。

比如现在有个脚本依赖于pymysql, openpyxl, 这时可以通过下面的命令下载好依赖库:

pip install --install-option="--prefix=./tmp_libs" pymysql
pip install --install-option="--prefix=./tmp_libs" openpyxl
mkdir libs
cp -r ./tmp_libs/.../site-packages/* libs/

最后修改脚本,在脚本开始执行前修改python的库路径:

import sys
import os
sys.path.append(os.path.join(os.path.dirname(os.path.realpath(__file__)), 'libs'))

写入excel文件

可以通过openpyxl写excel文件

workbook=openpyxl.Workbook()
sheet=workbook.active

....
cell=sheet.cell(row=i, column=j, value='xxxx')
cell.font=openpyxl.styles.Font(bold=True)
....

workbook.save('result.xlsx')

更多openpyxl的用法参见其文档