Python MySQL数据导入

Question

我使用以下脚本从第三方工具中提取数据，在MySQL数据库中创建一个表，并使用生成的数据填充该表。脚本运行完后，我可以在Python Shell窗口中看到所有请求数据的打印输出。但是，当我打开数据库时，将使用列名创建表，但没有行，也没有数据。我在周围搜索并读到，我不需要为一个只是检索数据的脚本使用'conn.commit‘。这里就是这样吗？如果没有，有没有人看到数据没有填充表的另一个原因？

import httplib2, urllib, json, pprint, getpass, string, time, MySQLdb

def usage():
    print "Usage: python26 mysql.py or ./mysql.py"
    sys.exit(1)

if len(sys.argv) != 1:
    usage()

# Connect to the database and create the tables
conn = MySQLdb.connect (host = "localhost",
                   user = "XXXXXXXXX",
                   passwd = "XXXXXXXX")
cursor = conn.cursor ()
cursor.execute ("DROP DATABASE IF EXISTS tenable")
cursor.execute ("CREATE DATABASE tenable")
cursor.execute ("USE tenable")
cursor.execute ("""
CREATE TABLE cumvulndata
(
  offset         BIGINT(10),
  pluginName     TEXT,
  repositoryID   SMALLINT(3),
  severity       TINYINT(2),
  pluginID       MEDIUMINT(8),
  hasBeenMitigated   TINYINT(1),
  dnsName        VARCHAR(255),
  macAddress     VARCHAR(40),
  familyID       INT(4),
  recastRisk     TINYINT(1),
  firstSeen      DATETIME,
  ip             VARCHAR(15),
  acceptRisk     TINYINT(1),
  lastSeen       DATETIME,
  netbiosName    VARCHAR(255),
  port           MEDIUMINT(5),
  pluginText     MEDIUMTEXT,
  protocol       TINYINT(3)
  )
  """)
#
# Security Center organizational user creds
user = 'XXXXXXXXX'
passwd = 'XXXXXXXX'
url = 'https://Security Center Server/request.php'

def SendRequest(url, headers, data):
    http = httplib2.Http()
    response, content = http.request(url, 
                                    'POST', 
                                     headers=headers, 
                                     body=urllib.urlencode(data))
    if 'set-cookie' in response:
       headers['Cookie'] = response['set-cookie']
    return response, content

headers = {"Content-type": "application/x-www-form-urlencoded"}

input = {'password': passwd,
     'username': user}

# Convert input to login JSON
inputjson = json.dumps(input)

data = {"request_id": "8",
    "module": "auth",
    "action": "login",
    "input": inputjson}

# Send Login Request
response, content = SendRequest(url, headers, data)

# Decode JSON to python data structure
result = json.loads(content)

if result["error_code"] == 0:
    print "SC4 Login Successful"
    token = result['response']['token']
    print "Session Token:",token

# Construct the cumulative vuln query JSON 
cuminput = {'tool':'vulndetails',
     'startOffset':'0',
     'endOffset':sys.maxint,
     'sortField':'ip',
     'sortDir':'asc',
     'sourceType':'cumulative',
     'filters': [
                            {'filterName':'lastSeen',
                            'value':'31',
                            'operator':'<='},
                            {"filterName":"severity",
                            "value":"1,2,3",
                            "operator":"="}

            ]}
cuminputjson = json.dumps(cuminput)

 #
cumdata = {"request_id": "1",
        "module": "vuln",
    "action": "query",
    "input":cuminputjson,
        "token": token}

# Send the cumulative JSON and then populate the table
cumresponse, content = SendRequest(url, headers, cumdata)
resultc = json.loads(content)
off = 0
    print "\nFilling cumvulndata table with vulnerabilities from the cumulative    database.     Please wait..."
for result in resultc['response']['results']:
    off += 1
    cursor.execute ("""INSERT INTO cumvulndata   (offset,pluginName,repositoryID,severity,pluginID,hasBeenMitigated,dnsName,macAddress,familyID,recastRisk,firstSeen,ip,acceptRisk,lastSeen,netbiosName,port,pluginText,protocol)
    VALUES
    (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,(FROM_UNIXTIME(%s)),%s,%s,(FROM_UNIXTIME(%s)),%s,%s,%s,%s)""",   (off,result["pluginName"],result["repositoryID"],result["severity"],result["pluginID"],result["hasBeenMitigated"],result["dnsName"],result["macAddress"],result["familyID"],result["recastRisk"],result["firstSeen"],result["ip"],result["acceptRisk"],result["lastSeen"],result["netbiosName"],result["port"],result["pluginText"],result["protocol"]))


# Close the cursor and connection
cursor.close ()
conn.close ()

print "Done!!"

Answer 1

尝尝这个

import httplib2, urllib, json, pprint, getpass, string, time, MySQLdb
import sys
def usage():
    print "Usage: python26 mysql.py or ./mysql.py"
    sys.exit(1)

if len(sys.argv) != 1:
    usage()

# Connect to the database and create the tables
conn = MySQLdb.connect (host = "localhost",
                   user = "XXXXXXXXXX",
                   passwd = "XXXXXXXX")
cursor = conn.cursor ()
cursor.execute ("DROP DATABASE IF EXISTS tenable")
cursor.execute ("CREATE DATABASE tenable")
cursor.execute ("USE tenable")
cursor.execute ("""
CREATE TABLE cumvulndata
(
  offset         BIGINT(10),
  pluginName     TEXT,
  repositoryID   SMALLINT(3),
  severity       TINYINT(2),
  pluginID       MEDIUMINT(8),
  hasBeenMitigated   TINYINT(1),
  dnsName        VARCHAR(255),
  macAddress     VARCHAR(40),
  familyID       INT(4),
  recastRisk     TINYINT(1),
  firstSeen      DATETIME,
  ip             VARCHAR(15),
  acceptRisk     TINYINT(1),
  lastSeen       DATETIME,
  netbiosName    VARCHAR(255),
  port           MEDIUMINT(5),
  pluginText     MEDIUMTEXT,
  protocol       TINYINT(3)
  )
  """)
cursor.execute ("""INSERT INTO cumvulndata   (offset,pluginName,repositoryID,severity,pluginID,hasBeenMitigated,dnsName,macAddress,familyID,recastRisk,firstSeen,ip,acceptRisk,lastSeen,netbiosName,port,pluginText,protocol)
    VALUES
    (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",   ('123','plugin','10','1','12','1',"dnsName","macAddress",'15','1','2011-2-2',"ip",'9','2012-5-2',"netbiosName",'123',"pluginText","2"))

#Commit the changes.
conn.commit()
cursor.close()
conn.close()

请commit更改，然后您将获得插入的数据。