背景：又要做狗血的數(shù)據(jù)遷移、數(shù)據(jù)清洗，每次面對(duì)此類(lèi)需求，心里都會(huì)把pm祖宗老老小小都問(wèn)候個(gè)遍。解決辦法，要么用java寫(xiě)一大堆支撐代碼，要么在vm上寫(xiě)蹩腳的shell，都很不爽。最近嘗試了下python解決此類(lèi)問(wèn)題，有點(diǎn)小爽，心中安喜 : "BB,我再也不怕pm這些腦殘需求了"。

環(huán)境準(zhǔn)備: Ubuntu 13.04。

???????? #建議至少安裝Python2.7/3.2版本，畢竟同Python 2.X/3.x還是有區(qū)別的

???????? sudo apt-get install python2.7 python2.7-dev

???????? #安裝libssl和libevent編譯環(huán)境
?????????????? sudo apt-get install build-essential libssl-dev libevent-dev libjpeg-dev libxml2-dev libxslt-dev

?????????????? #安裝mysqldb

?????????????? sudo easy_install mysql-python

?????????????? #測(cè)試

?????????????? whereis python | python -V

Python開(kāi)始: 有了上面的環(huán)境準(zhǔn)備，就可以書(shū)寫(xiě)pthon了。創(chuàng)建python文件,touch firstPython.py。文件名是firstPython，擴(kuò)展名是py。編輯此文件，類(lèi)似php、java，python也有自己的函數(shù)庫(kù)。

?????????????????

???????????????? 主方法，python文件被執(zhí)行的入口，

????????????????

???????????????? 讀取主方法傳入的參數(shù)，sys.argv返回的是一個(gè)參數(shù)數(shù)組，sys.argv[index]。

????????????????

???????????????? 定義成員方法，在主方法中調(diào)用執(zhí)行。

????????????????

???????????????? profile是成員方法的參數(shù)，由于python是弱語(yǔ)言類(lèi)型，所以變量不需要聲明類(lèi)型，這點(diǎn)有別于強(qiáng)語(yǔ)言類(lèi)型c++、java。

???????????????? 由于我的需求背景是數(shù)據(jù)遷移，所以在python中有效的訪問(wèn)數(shù)據(jù)庫(kù)很重要。

????????????????

???????????????? 看到這個(gè)數(shù)據(jù)庫(kù)握手連接、fetch數(shù)據(jù)，是不是感到很方便、簡(jiǎn)潔，和php一樣的類(lèi)庫(kù)風(fēng)格。

???????????????? python數(shù)據(jù)結(jié)構(gòu)，內(nèi)置類(lèi)型

???????????????? 1、list：列表（動(dòng)態(tài)數(shù)組， c++標(biāo)準(zhǔn)庫(kù)的vector，可以在一個(gè)列表中包含不同類(lèi)型的元素）

????????????????

???????????????? 列表下標(biāo)從0開(kāi)始，－1是最后一個(gè)元素。取list的元素?cái)?shù)量:len(list)。

???????????????? 創(chuàng)建連續(xù)的list

???????????????? L.append(var)? #追加元素

???????????????? L.insert(index,var)

???????????????? L.pop(var)?? #返回最后一個(gè)元素，并從list中刪除

???????????????? L.remove(var)???#刪除第一次出現(xiàn)的該元素?

???????????????? L.count(var)????#該元素在列表中出現(xiàn)的個(gè)數(shù)?

???????????????? L.index(var)????#該元素的位置,無(wú)則拋異常??

???????????????? L.extend(list)??#追加list，即合并list到L上?

???????????????? L.sort()????????#排序?

???????????????? L.reverse()?????#倒序

???????????????? 2、dictionary 字典(c++標(biāo)準(zhǔn)庫(kù)的map)

????????????????

???????????????? 每一個(gè)元素是一個(gè)pair鍵值對(duì)，key是Integer或String類(lèi)型，value是任意類(lèi)型。

???????????????? dictionary的方法:?
???????????????? D.get(key, 0)?????? #同dict[key]，多了個(gè)沒(méi)有則返回缺省值，0。[]沒(méi)有則拋異常 ?
???????????????? D.has_key(key)????? #有該鍵返回TRUE，否則FALSE ?
???????????????? D.keys()??????????? #返回字典鍵的列表 ?
???????????????? D.values()????????? #以列表的形式返回字典中的值，返回值的列表中可包含重復(fù)元素 ?
???????????????? D.items()?????????? #將所有的字典項(xiàng)以列表方式返回，這些列表中的每一項(xiàng)都來(lái)自于(鍵,值),但是項(xiàng)在返回時(shí)并沒(méi)有特殊的順序????????? ?
?
???????????????? D.update(dict2)???? #增加合并字典 ?
???????????????? D.popitem()???????? #得到一個(gè)pair，并從字典中刪除它。已空則拋異常 ?
???????????????? D.clear()?????????? #清空字典，同del dict ?
???????????????? D.copy()??????????? #拷貝字典 ?
???????????????? D.cmp(dict1,dict2)? #比較字典，(優(yōu)先級(jí)為元素個(gè)數(shù)、鍵大小、鍵值大小) ?第一個(gè)大返回1，小返回-1，一樣返回0?
???????????? ?
???????????????? dictionary的復(fù)制 ?
???????????????? dict1 = dict??????? #別名 ?
???????????????? dict2=dict.copy()?? #克隆，即另一個(gè)拷貝。

?

示例代碼：

import MySQLdb
import time
import datetime
import sys,os

hms_connections = {}
transfer_connections = {}
totalResult = []
def queryFromHms():
??? print "query from hms beginning..."
??? db=MySQLdb.connect(host=hms_connections.get('host'),user=hms_connections.get('user'),passwd=hms_connections.get('passwd'),db=hms_connections.get('db'),port=hms_connections.get('port'))
??? try:
??????? cursor = db.cursor()
??????? resultPerDay = {}
??????? sql = "select a.user_id,a.hotel_id,a.parent_group_id? from hotel_sub_account a inner join lm_transfer_hotel b on a.hotel_id = b.hotel_id and b.QTA_STATUS=1"
??????? print sql
??????? cursor.execute(sql)
??????? results = cursor.fetchall()
??????? for row in results:
?????????? print row
?????????? user_id? = row[0]
?????????? hotel_id =? row[1]
?????????? parent_group_id = row[2]
?????????? totalResult.append({"user_id":user_id, "hotel_id" : hotel_id, "parent_group_id" : parent_group_id})
??????? cursor.close();
??? finally:
??????? db.close();
??????? print "function queryFromHms to close db connection...";

def queryFromTransfer():
??? print "query from transfer beginning..."
??? db=MySQLdb.connect(host=transfer_connections.get('host'),user=transfer_connections.get('user'),passwd=transfer_connections.get('passwd'),db=transfer_connections.get('db'),port=transfer_connections.get('port'))
??? try:
??????? for row in totalResult:
??????????? cursor = db.cursor()
??????????? sql = "select qta_id,hms_id from mapping_hms_qta_price where hms_level=1 and qta_level=1 and hms_id = %s" %(row.get('parent_group_id'))
??????????? print sql
??????????? cursor.execute(sql)
??????????? results = cursor.fetchall()
??????????? for subrow in results:
??????????????? print subrow
??????????????? row["qta_id"]= subrow[0]
??????????? cursor.close();
??? finally:
??????? db.close();
??????? print "function queryFromTransfer to close db connection...";



def outputSupplierAccount():
??? print "output sql to supplier_account..."

??? upgradeSql?? = "insert into supplier_account? (`supplier_id`, `account`, `create_time`, `is_delete`) values(%(qta_id)s, '%(user_id)s', now(), 0); \n"
??? callbackSql? = "delete from supplier_account where supplier_id = %(qta_id)s and account = '%(user_id)s'; \n"
??? upgradeFile? = open("qta_upgrade.sql", "w")
??? callbackFile = open("qta_callback.sql", "w")

??? for row in totalResult:
??????? upgradeFile.write(upgradeSql%row)
??????? callbackFile.write(callbackSql%row)

??? upgradeFile.close()
??? upgradeFile.close()


def outputUserHotelMapping():
??? print "output sql to eb_auth_user_hotel_mapping..."

??? upgradeSql?? = "insert into eb_auth_user_hotel_mapping (`user_name`, `hotel_id`, `create_time`, `hotel_seq`, `supplier_id`, `group_id`) values('%(user_id)s', '', now(), '', %(qta_id)s, %(parent_group_id)s); \n"
??? callbackSql? = "delete from eb_auth_user_hotel_mapping where `user_name`='%(user_id)s' and `supplier_id`=%(qta_id)s and `group_id`=%(parent_group_id)s; \n"
??? upgradeFile? = open("hms_upgrade.sql", "w")
??? callbackFile = open("hms_callback.sql", "w")
? ?
??? for row in totalResult:
??????? upgradeFile.write(upgradeSql%row)
??????? callbackFile.write(callbackSql%row)

??? upgradeFile.close()
??? upgradeFile.close()


def outputUserUriMapping():
??? print "output sql to eb_auth_user_uri_mapping..."

??? upgradeFile? = open("hms_upgrade.sql", "a")
??? callbackFile = open("hms_callback.sql", "a")
??? uris = [1,2,3,5,6,7,8,9,10,21,22,24,34,35,36,37,40,41,42,43,44,46,47,49,50,54,55,56,57,58,59,60,61,62,63,76,77,78,79]

??? for row in totalResult:
??????? for uri in uris:
??????????? upgradeSql?? = "insert into eb_auth_user_uri_mapping(`user_name`, `uri_id`, `create_time`) values('%s', %s, now()); \n" %(row['user_id'], uri)
??????????? callbackSql? = "delete from eb_auth_user_uri_mapping where user_name='%s' and uri_id=%s; \n" %(row['user_id'], uri)
??????????? upgradeFile.write(upgradeSql%row)
??????????? callbackFile.write(callbackSql%row)

??? upgradeFile.close()
??? upgradeFile.close()


def configDbProfile(profile):
??? print "current DB profile is %s" %(profile)
??? if profile == "beta":
?????? hms_connections['host'] = ""
?????? hms_connections['user'] = ""
?????? hms_connections['passwd'] = ""
?????? hms_connections['db'] = ""
?????? hms_connections['port'] = 3306

?????? transfer_connections['host'] = ""
?????? transfer_connections['user'] = ""
?????? transfer_connections['passwd'] = ""
?????? transfer_connections['db'] = "data_transfer"
?????? transfer_connections['port'] = 3306

??? elif profile == "product":
?????? hms_connections['host'] = ""
?????? hms_connections['user'] = ""
?????? hms_connections['passwd'] = ""
?????? hms_connections['db'] = "hms"
?????? hms_connections['port'] = 3307

?????? transfer_connections['host'] = ""
?????? transfer_connections['user'] = ""
?????? transfer_connections['passwd'] = ""
?????? transfer_connections['db'] = ""
?????? transfer_connections['port'] = 3307

??? elif profile == "productb":
?????? hms_connections['host'] = ""
?????? hms_connections['user'] = ""
?????? hms_connections['passwd'] = ""
?????? hms_connections['db'] = "hms"
?????? hms_connections['port'] = 3307

?????? transfer_connections['host'] = ""
?????? transfer_connections['user'] = ""
?????? transfer_connections['passwd'] = ""
?????? transfer_connections['db'] = "data_transfer"
?????? transfer_connections['port'] = 3308

??? else:
?????? print "input parameter invalid, choose (beta | product | productb)"
?????? sys.exit(0)


if? __name__ == '__main__':
?? if len(sys.argv) != 2:
????? print "please input parameter : (beta | product | productb)"
????? sys.exit(0)

?? profile = sys.argv[1] ?
?? configDbProfile(profile)
?? queryFromHms();
?? queryFromTransfer();
?? outputSupplierAccount();
?? outputUserHotelMapping();
?? outputUserUriMapping();

轉(zhuǎn)載于:https://www.cnblogs.com/qinpeirong/p/3824193.html

總結(jié)

以上是生活随笔為你收集整理的python之初体验(一)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。