本篇內(nèi)容主要講解“python怎么讓數(shù)據(jù)庫(kù)的表遷移到另一臺(tái)服務(wù)器”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“python怎么讓數(shù)據(jù)庫(kù)的表遷移到另一臺(tái)服務(wù)器”吧!
安裝MySQL-python
要想使python可以操作mysql 就需要MySQL-python驅(qū)動(dòng)，它是python 操作mysql必不可少的模塊。
下載地址：https://pypi.python.org/pypi/MySQL-python/
下載MySQL-python-1.2.5.zip 文件之后直接解壓。進(jìn)入MySQL-python-1.2.5目錄:
>>python setup.py install
報(bào)錯(cuò)：
[root@S220 MySQL-python-1.2.5]# python setup.py install
sh: mysql_config: command not found
Traceback (most recent call last):
File "setup.py", line 17, in
metadata, options = get_config()
File "/MySQL-python-1.2.5/setup_posix.py", line 43, in get_config
libs = mysql_config("libs_r")
File "/MySQL-python-1.2.5/setup_posix.py", line 25, in mysql_config
raise EnvironmentError("%s not found" % (mysql_config.path,))
EnvironmentError: mysql_config not found
解決辦法：手工安裝mysql_python
在setup_posix.py內(nèi)找到mysql_config.path=/xxx/xxx(mysql安裝路徑/bin/mysql_config)
如下：
[root@S220 MySQL-python-1.2.5]# vi setup_posix.py
import os, sys
from ConfigParser import SafeConfigParser
# This dequote() business is required for some older versions
# of mysql_config
def dequote(s):
if s[0] in "\"'" and s[0] == s[-1]:
s = s[1:-1]
return s
def compiler_flag(f):
return "-%s" % f
def mysql_config(what):
from os import popen
f = popen("%s --%s" % (mysql_config.path, what))
data = f.read().strip().split()
ret = f.close()
if ret:
if ret/256:
data = []
if ret/256 > 1:
raise EnvironmentError("%s not found" % (mysql_config.path,))
return data
mysql_config.path ='/home/mysql/bin/mysql_config'
def get_config():
from setup_common import get_metadata_and_options, enabled, create_release_file
之后：
[root@S220 MySQL-python-1.2.5]# python setup.py install
running install
。
。
。
。
。
在包含自 _mysql.c：44 的文件中:
/home/mysql/include/my_config.h:442:1: 警告：“HAVE_WCSCOLL”重定義
在包含自 /usr/local/include/python2.7/Python.h：8 的文件中，
從 _mysql.c：29:
/usr/local/include/python2.7/pyconfig.h:902:1: 警告：這是先前定義的位置
copying build/lib.linux-x86_64-2.7/MySQLdb/constants/__init__.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
Processing dependencies for MySQL-python==1.2.5
Finished processing dependencies for MySQL-python==1.2.5
至此MySQL-python驅(qū)動(dòng)安裝成功。。。。。。
把mysql查出的內(nèi)容放到一個(gè)列表中：
[root@S220 ~]# cat mysqldump.py
#!/usr/bin/python
#-*_ coding:UTF-8 _*_
import MySQLdb
conn= MySQLdb.connect(
host='192.168.0.144',
port = 3306,
user='root',
passwd='admin',
db ='jf',
)
cur = conn.cursor()
dbs=cur.execute('show databases')
mail_list=[]
#獲取所有結(jié)果
databases = cur.fetchall()
result=list(databases)
for r in result:
mail_list.append(r)
print mail_list
[root@S220 ~]# python mysqldump.py
[('ad',), ('agency',), ('beifen',), ('chinabidding',), ('crm',), ('crm2',), ('data',), ('dingyue',), ('experience',), ('fxb2016',), ('hdzq',), ('history',), ('info',), ('info_addition',), ('info_back',), ('information_schema',), ('infoservice',), ('jf',), ('jf2016',), ('liuwenhe',), ('log',), ('mailer',), ('mysql',), ('performance_schema',), ('resin_session',), ('sbiao',), ('sbw2',), ('stat',), ('test',), ('web',), ('weblog',), ('winfo',)]
上面的結(jié)果中是一個(gè)列表中又有一個(gè)元祖，這不是我們想要的結(jié)果，所以先循環(huán)列表再循環(huán)元祖，這樣可以真正做到把數(shù)據(jù)庫(kù)查出的內(nèi)容存到一個(gè)列表中。如下紅色部分。
[root@S220 ~]# cat mysqldump.py
#!/usr/bin/python
#-*_ coding:UTF-8 _*_
import MySQLdb
conn= MySQLdb.connect(
host='192.168.0.144',
port = 3306,
user='root',
passwd='admin',
db ='jf',
)
cur = conn.cursor()
dbs=cur.execute('show databases')
mail_list=[]
#獲取所有結(jié)果
databases = cur.fetchall()
result=list(databases)
for r in result:
mail_list.append(r)
dbs_list=[]
for db in mail_list:
for db1 in db:
dbs_list.append(db1)
print(dbs_list)
下面是寫(xiě)的一個(gè)python腳本，用來(lái)完成從一臺(tái)mysql服務(wù)器遷移到另一臺(tái)mysql服務(wù)器，一個(gè)表一個(gè)表的遷移，這試用于搭建主從，或者搭建測(cè)試環(huán)境的場(chǎng)景。我這個(gè)未來(lái)便于理解，寫(xiě)的有點(diǎn)多余，其實(shí)不需要把取出來(lái)的庫(kù)和下面的表名字，放到一個(gè)列表中這2步驟。
[root@S220 ~]# cat mysqldump.py
#!/usr/bin/python
#-*_ coding:UTF-8 _*_
import MySQLdb
import os
conn= MySQLdb.connect(
host='192.168.0.144',
port = 3306,
user='root',
passwd='***',
db ='jf',
)
cur = conn.cursor()
dbs=cur.execute('show databases')
mail_list=[]
#獲取所有結(jié)果
databases = cur.fetchall()
result=list(databases)
for r in result:
mail_list.append(r)
dbs_list=[]
for db in mail_list:
for db1 in db:
dbs_list.append(db1)
conn.close()
cur.close()
tables_list=[]
for db2 in dbs_list:
if db2=='liuwenhe':
conn_name={"host":"192.168.0.**","user":"root","passwd":"ad****min","db":db2}
conn1= MySQLdb.connect(**conn_name)
cur_new = conn1.cursor()
tbs=cur_new.execute('show tables')
tbs_list=[]
tables = cur_new.fetchall()
result=list(tables)
for t in result:
tbs_list.append(t)
for tb1 in tbs_list:
for tb2 in tb1:
tables_list.append(tb2)
conn1.close()
cur_new.close()
for table in tables_list:
dumpcmd = "/home/mysql/bin/mysqldump -uroot " + " --opt " " -padmin " +" -h292.168.0.** " +"-d " +db2 +" "+table +" > " + "/backup/" + db2 +"_"+table+ ".sql"
os.system(dumpcmd)
sql1="/home/mysql/bin/mysql "+"-uroot "+"-pg****in@123 "+"-h292.168.0.220 "+db2 +"
os.system(sql1)
print "data translate completed"
到此，相信大家對(duì)“python怎么讓數(shù)據(jù)庫(kù)的表遷移到另一臺(tái)服務(wù)器”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢(xún)，關(guān)注我們，繼續(xù)學(xué)習(xí)！
重慶分公司
重慶分公司
