mercredi 22 juin 2016

MySQLdb is not filling rows in DB with data, but feildes are auto-incremented

I am using MySQlDb to connect and populate my DB with a python script. Data from a BGP stream (dump) is going in to the DB. But when I try to execute(insert data) with SQL on line 65 in the bottom of the code, the DB is not affected, besides that the row does auto-increment on one of it’s fields. Is this a encoding issue? I am using utf-8 in python and utf-8, utf8_swedish_ci in MySQL. Code I am using:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
from _pybgpstream import BGPStream, BGPRecord, BGPElem
from collections import defaultdict
import time
import datetime
import os
import MySQLdb

db = MySQLdb.connect(user="bgpstream", host="localhost",   passwd="Bgpstream9", db="bgpstream_copy")
db_cursor = db.cursor()

# Create a new bgpstream instance and a reusable bgprecord instance
stream = BGPStream()    
rec = BGPRecord()

# Consider Route Views origon only
collector_name = 'rrc11'
stream.add_filter('collector',collector_name)   #maybe we want route-views4?

t_end = int(time.time())    #current time now   
t_start = t_end-3600 #the time interval (duration) we are getting    from collecor, e.i 60*60 = 3600s = 1 hour
stream.add_interval_filter(t_start,t_end)
print "Total duration " + str(t_end-t_start) + " sec"

# Start the stream
stream.start()

### Insert loop ###
# This loop insert new records and tries not to over count the records
# Get next record:
while(stream.get_next_record(rec)):
    # Print the record information only if it is not a valid record
    if rec.status != "valid":
        print rec.project, rec.collector, rec.type, rec.time, rec.status
    else:

        # Skip if rib
        if rec.type == "rib":
            continue

        #get affected rows from insert
        affected_rows = db.affected_rows()

        # Skip if dulpicate record 
        if affected_rows <= 0:
            continue

        # Extract insert id of last inserted bgp record
        last_record_id = db.insert_id()

        print last_record_id

        # Traverse elements
        elem = rec.get_next_elem()
        while(elem):

            print last_record_id

            ## Dette bør kaste en exeption
            if elem == None:
                continue

            # Insert element
            db_cursor.execute(
            """INSERT INTO bgp_elements
            (record_id_owner, element_time, peer_address, peer_asn) 
            VALUES 
            (
            '"""+str(last_record_id)+"""',
            '"""+str(elem.time)+"""',
            '"""+str(elem.peer_address)+"""',
            '"""+str(elem.peer_asn)+"""'
            ) 
            """)            
            elem = rec.get_next_elem()

Aucun commentaire:

Enregistrer un commentaire