SQL:不存在则insert存在则update
参考资料:http://www.tuicool.com/articles/YR7FNf
INSERT INTO arfcns(arfcn,power,type) VALUES('11','22','33') ON DUPLICATE KEY UPDATE counter=counter+1;
执行update语句的条件是insert语句的执行会造成唯一键的重复。
所以在创建表的时候还要提前加上唯一键的约束 :
ALTER TABLE arfcns ADD CONSTRAINT c_arfcn UNIQUE(arfcn);
表的DDL:
CREATE TABLE `arfcns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`arfcn` varchar(20) NOT NULL,
`power` varchar(20) NOT NULL,
`type` varchar(20) NOT NULL,
`describe` varchar(256) DEFAULT NULL,
`counter` int(11) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `c_arfcn` (`arfcn`)
) ENGINE=MyISAM AUTO_INCREMENT=227 DEFAULT CHARSET=utf8
python代码示例:
def save_arfcns(arfcn,power,sptype):
mysql = Database()
mysql.insert("SET NAMES utf8")
mysql.insert("INSERT INTO arfcns(arfcn,power,type) VALUES('%s', '%s','%s') on duplicate key update counter=coun
ter+1" % (str(arfcn).encode('utf-8'), str(power).encode('utf-8'),str(sptype).encode('utf-8')))