数据库批量插入使用

# coding=utf-8
import time

import psycopg2
from psycopg2 import extras as ex
from psycopg2.extensions import AsIs, ISQLQuote

# 将json插入user表中
conn = psycopg2.connect(host='16.**.**.**', port=****, user='db_uesrname',
                        password='db_password', database='da_name')
cursor = conn.cursor()
data_values = list()
class IP:
    def __init__(self,  value1):
        self.key1= value1
        
    def __len__(self):
        return 1

    def __getitem__(self, key):
        return self

    def __conform__(self, protocol):
        if protocol is ISQLQuote:
            return AsIs(
                "'{key1}'".  format(key1=self.value1))
        return None

start_append = time.time()
p1 = IP('0')
for i in range(100000):
    data_values.append(p1)
end_appeng = time.time()
time_insert_1w_append = end_appeng - start_append
print time_insert_1w_append
print len(data_values)

for i in range(1):
    start_append = time.time()
    sql = "INSERT INTO tablename (key1) VALUES %s"
    ex.execute_values(cursor, sql, data_values, page_size=10000)
    conn.commit()
    end_appeng = time.time()
    time_insert_1w_speed = end_appeng - start_append
    print 'time_insert_1w_speed is ' + str(time_insert_1w_speed)
conn.close()