Python MySQLdb使用，以及MySQL 与 PostgreSQL 数据互传

python mysql编程
http://www.cnblogs.com/coser/archive/2012/01/12/2320741.html
http://www.cnblogs.com/rollenholt/archive/2012/05/29/2524327.html
http://www.th7.cn/Program/Python/201411/320103.shtml
http://oruja.iteye.com/blog/277281
http://www.jb51.net/article/57290.htm
mysql语句
http://www.cnblogs.com/yunf/archive/2011/04/12/2013448.html
postgresql中文文档
http://www.php100.com/manual/PostgreSQL8/datatype.html

1.安装

下载地址
https://sourceforge.net/projects/mysql-python/
对于windows 直接下载exe安装即可
对于ubuntu
然后解压，打开README：
参考
http://www.cnblogs.com/rollenholt/archive/2012/05/07/2487137.html
里面有安装过程：

  $ tar xfz MySQL-python-1.2.1.tar.gz   $ cd MySQL-python-1.2.1   $ # edit site.cfg if necessary   $ python setup.py build   $ sudo python setup.py install # or su first

不过在python setup.py build时报错：

ImportError: No module named setuptools

ubuntu下安装：

sudo apt-get install python-setuptools

python-setuptools ： Python Distutils Enhancements (setuptools compatibility)

然后再次python setup.py build，又报错：

EnvironmentError: mysql_config not found

因为mysql_config是属于MySQL开发用的文件，而使用apt-get安装的MySQL是没有这个文件的，于是在包安装器里面寻找

sudo apt-get install  libmysqld-dev

libmysqld-dev ： MySQL embedded database development files

再次运行python setup.py build，报错：

building ‘_mysql’ extension gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -Dversion_info=(1,2,3,’final’,0) -D__version__=1.2.3 -I/usr/include/mysql -I/usr/include/python2.7 -c _mysql.c -o build/temp.linux-i686-2.7/_mysql.o -DBIG_JOINS=1 -fno-strict-aliasing -DUNIV_LINUX -DUNIV_LINUX In file included from _mysql.c:29:0: pymemcompat.h:10:20: fatal error: Python.h: No such file or directory

解决方案，

sudo apt-get install python-dev

python-dev ： header files and a static library for Python (default)

然后就按照README里的：

  $ python setup.py build   $ sudo python setup.py install

测试：

>>>import MySQLdb

没有报错即可。

2.mySQL 基础操作

参考
http://zhidao.baidu.com/question/96085203.html
查看数据库列表

show databases; 

打开数据库

use data 

能转到数据库

3.获取表结构

我们这里需要实现的是将mySQL中的一个表迁移到PostgreSQL
所以这里首先获取表结构（Navicat for mysql）

CREATE TABLE `plugin` (  `id` int(11) NOT NULL DEFAULT '0',  `xxxxx` bigint(20) DEFAULT NULL,  `xxxxx` bigint(20) DEFAULT NULL,  `xxxxx` varchar(255) DEFAULT NULL,  `xxxxx` varchar(128) DEFAULT NULL,  `xxxxx` varchar(128) DEFAULT NULL,  `familiar` int(11) DEFAULT NULL,  `categories` longtext,  `severity_points` smallint(6) DEFAULT NULL,  `is_banned` varchar(10) DEFAULT NULL,  `is_dangerous` varchar(10) DEFAULT NULL,  `date_recorded` date DEFAULT NULL,  `date_found` date DEFAULT NULL,  `i18n_name` longtext,  `i18n_description` longtext,  `i18n_solution` longtext,  `i18n_morelinks` longtext,  `i18n_patch` longtext,  `cnnvd` longtext,  `cncve` longtext,  `cvss_base_score` longtext,  `cnvd` longtext) ENGINE=MyISAM DEFAULT CHARSET=utf8;

首先，查看该SQL语句是否能在Postgre中正确运行
发现出现了一下问题
1.反引号报错，删掉
2.类型后面的数字报错(10) 删去
3.longtext 类型不存在，改成text（MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text… Pg 没有这个限制, text 能支持各种大小）
4.结尾句无法识别，删去
得到

CREATE TABLE plugin (   id  int NOT NULL DEFAULT 0,   xxxxx  bigint DEFAULT NULL,   xxxxx  bigint DEFAULT NULL,   xxxxx  varchar DEFAULT NULL,   xxxxx  varchar DEFAULT NULL,   xxxxx  varchar DEFAULT NULL,   familiar  int DEFAULT NULL,   categories   text,   severity_points  smallint DEFAULT NULL,   is_banned  varchar DEFAULT NULL,   is_dangerous  varchar DEFAULT NULL,   date_recorded  date DEFAULT NULL,   date_found  date DEFAULT NULL,   i18n_name  text,   i18n_description  text,   i18n_solution  text,   i18n_morelinks  text,   i18n_patch  text,   cnnvd  text,   cncve  text,   cvss_base_score  text,   cnvd  text);

可以在postgresql中正确执行
至此得到正确表结构

表结构得到了，就可以在mysql读取信息，传到postgresql中了
首先实现一条信息的传递

# -*- coding: utf-8 -*-"""Created on Fri Mar 11 10:27:04 2016@author: wangxin2"""import MySQLdbimport psycopg2# 数据库连接参数db=MySQLdb.connect(host="X.X.X.X"\                    ,user="X"\                    ,passwd="X"\                    ,db="X"\                    ,charset="utf8")cursor = db.cursor()# 数据库连接参数db_new = psycopg2.connect(database="usr",\                         user="usr", \                         password="usr",\                         host="X",\                         port="5432")cursor_new = db_new.cursor()cursor.execute("select * from plugin where id =%s;",(11000))data=cursor.fetchone()cursor_new.execute("INSERT INTO plugin1(id,XXXX,XXXX_id,XXXXX_id,XXXX_id,XXXXX_id,familiar,categories,\severity_points,is_banned,is_dangerous,date_recorded,date_found,i18n_name,i18n_description,i18n_solution,\i18n_morelinks,i18n_patch,cnnvd,cncve,cvss_base_score,cnvd) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)#简单说就是通过select 读取数据表中的一行为list，获取该list之后#再传到postgresql中db_new.commit()#确认数据库修改cursor_new.close()db_new.close()

这里碰到了以下问题：
1.postgresql远程允许访问IP设置错误，修改后问题解决

 conn = _connect(dsn, connection_factory=connection_factory, async=async)psycopg2.OperationalError: could not connect to server: Connection refused (0x0000274D/10061)    Is the server running on host "10.24.35.103" and accepting    TCP/IP connections on port 5432?

2.弄错了获取的data的格式，execute后面%s对应的应该是一项而不是一个list，这里需要多个%s，根据表的列数。

cursor_new.execute("INSERT INTO plugin %s",data)TypeError: not all arguments converted during string formatting

3.复制%s时有的%号后面落了s

 i18n_morelinks,i18n_patch,cnnvd,cncve,cvss_base_score,cnvd) VALUES(%s,%s,%s,%,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)ValueError: unsupported format character ',' (0x2c) at index 270

4.弄错了表名

i18n_morelinks,i18n_patch,cnnvd,cncve,cvss_base_score,cnvd) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)psycopg2.ProgrammingError: relation "plugin" does not existLINE 1: INSERT INTO plugin(id,plugin_id,vul_id,cve_id,bugtraq_id,nsf...

5.未以list形式提供execute函数对应的第二个传参

cursor_new.execute("INSERT INTO plugin(id,plugin_id) values(%s,%s)",data[0],data[1])TypeError: function takes at most 2 arguments (3 given)

6.建表的时候默认建成了owner为postgres，而非连入的usr用户，这里访问的时候请求延迟，改正很简单。

 cursor_new.execute("INSERT INTO plugin1(id,plugin_id) values(%s,%s)",(1,2))psycopg2.ProgrammingError: permission denied for relation plugin1

alter table plugin1 owner to usr

将owner改为usr，问题解决。
最后就跑通了，数据可以互传。

4 传一个完整的表

表结构的建立上面已经完整给出，传一行数据也已经实现，那么接下来我们考虑传一整个表的数据。
开始考虑从id下手，通过select不同id对应的数字，存入表中，后来仔细阅读需要传输的表，发现有个问题，就是id并不是主键。也不是自增的，表中数据并非是id从1到最后
所以就不考虑一条一条select了，直接select * from plugin
那么表中的数据就都获取了，再利用函数cursor.fetchone（），获取每一行的数据，这里需要注意的是fetchone函数每一次调用都会导致光标的下移，也就是说只要我们不断的调用这个函数，直到获取不到数据即可。
完整代码如下：

# -*- coding: utf-8 -*-"""Created on Fri Mar 11 10:27:04 2016@author: wangxin2"""import MySQLdbimport psycopg2# 数据库连接参数db=MySQLdb.connect(host="X.X.X.X"\                    ,user="XX"\                    ,passwd="XXXXXX"\                    ,db="XXX"\                    ,charset="utf8")cursor = db.cursor()# 数据库连接参数db_new = psycopg2.connect(database="usr",\                         user="usr", \                         password="usr",\                         host="X.X.X.X",\                         port="5432")cursor_new = db_new.cursor()cursor.execute("select * from plugin;")flag=True;while flag:    data=cursor.fetchone()    if data != None:        cursor_new.execute("INSERT INTO plugin1(id,XXXX,XXXX_id,XXXXX_id,XXXX_id,XXXXX_id,familiar,categories,\severity_points,is_banned,is_dangerous,date_recorded,date_found,i18n_name,i18n_description,i18n_solution,\i18n_morelinks,i18n_patch,cnnvd,cncve,cvss_base_score,cnvd) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)        db_new.commit()    else:        flag=Falsecursor_new.close()db_new.close()

很简单的就讲一个表穿过来了。
遇到的问题
1.开始未弄清楚当表中光标对应的行为空的时候，fetchone传回的是什么。由于一般的情况传回来的都是一个元组，以为空的时候传回来的就是一个空的元组,

if len(data):    ...

使用长度查询结果报错，说是NoneType类型的数据没定义len函数，那么就考虑是不是可以用type函数来判断是否到最后一行呢，结果又报错

if type(data) != NoneType:NameError: name 'NoneType' is not defined

查了以后发现，原来上面的NoneType并不是一种数据类型
而是说

data=None

所以最后使用

if data != None:    ...

实现的表的迁移。

思考，

cursor.execute("select * from plugin;")

究竟是如何存储数据的了什么？是将select的数据又建立了一个临时的新表供我们查阅么？我们是不是只是通过控制光标cursor来实现操作的？