Python operation MySQL Database

Available in three ways: Mysql-connector, Mysqldb,torndb (two packages for mysqldb)

Mysql-connector: Cursors are weak references and cannot be encapsulated into functions

MYSQLDB: can be encapsulated into functions

TORNDB: The return value can be converted into a dictionary

from __future__ import print_functionsql =  (' Select * from ipdata  limit 10 ') # mysql-connectorprint (' Mysql-connector '. Center (50,  ' = ')) from mysql  Import connectorcnx = connector. Connect (host= "localhost",  user= "root",                             password = "",  database= "test",  charset= "UTF8") # cnx.autocommit = truedb0 =  Cnx.cursor () db0.execute (SQL) For row in db0:    print (*row)  #  Print row[0], row[1], row[2], row[3]# mysqldbprint (' MySQLdb '. Center (50,  ' = ')) Import mysqldbdef connect_mysql (db_host= "localhost",  user= "root",                    passwd= "", DB= "Test",  charset= "UTF8"):     conn = mysqldb.connect (Host=db_host, user =user, passwd=passwd, db=db, charset=charset)     conn.autocommit (True)     return conn.cursor () Db1 = connect_mysql () db1.execute (SQL) For row  in db1:    print (*row) # torndb1print (' torndb1 '. Center (50,  ' = ')) Import  torndbimport simplejson as jsondb2 = torndb. Connection (    host= ' localhost ',     database= ' test ',     user= ' root ',     password= ',     charset= "UTF8") rows =  Db2.query (SQL) For row in rows:    print (json.dumps (row, ensure_ascii= False)) # # torndb2# print (' torndb3 '. Center (50,  ' = ')) # row = db2.get (SQL) #  print (Json.dumps (Row, ensure_ascii=false)) # # torndb3print (' torndb2 '. Center (50,  ' = ')) row = db2.get (' Select * from ipdata  limit 1 ') print (Json.dumps (row, ensure_ascii=false))

The Get method can only return a single piece of data, returning multiple errors

BULK INSERT Data

# ! /usr/bin/python# -*- coding: utf-8 -*-sql =  ' INSERT INTO   ' Ipdata '   (' StartIP ', ' EndIP ', ' country ', ' carrier ')  VALUES  (18684928,18684928, "Inner Mongolia Chifeng Bairin Zuoqi", " Unicom Lindong New urban Bras Data Room ") ' sql_tmp =  ' insert into  ' ipdata '   (' StartIP ', ' EndIP ', ' Country ', ' Carrier ')  VALUES  (%s, %s, %s, %s) ' values = [(16890112,16891391, "Thailand", "Bangkok"), ( 16891392,16891647, "Thailand", "if Silicon Farmer"),  (16891648,16892159, "Thailand", "the province of La Thaksin")]# mysql-connectorprint (' Mysql-connector '. Center (50,  ' = ')) From mysql import connectorcnx = connector. Connect (host= "localhost",  user= "root",                             password = "",  database= "test",  charset= "UTF8") cnx.autocommit = truedb0 = cnx.cursor () Print db0.execute (SQL) print db0.executemany (sql_tmp, values) # mysqldbprint (' MySQLdb '. Center (50,  ' = ')) import mysqldbdef  connect_mysql (db_host= "localhost",  user= "root",                    passwd= "", db= "test",  charset= "UTF8"):     conn = mysqldb.connect (HOST=DB_HOST, USER=USER, PASSWD=PASSWD,  db=db, charset=charset)     conn.autocommit (True)     return  conn.cursor () Db1 = connect_mysql () print db1.execute (SQL), db1.lastrowidprint  Db1.executemany (Sql_tmp, values),  db1.lastrowid# torndbprint (' torndb1 '. Center (50,  ' = ')) Import torndbdb2 = torndb. Connection (    host= ' localhost ',     database= ' test ',     user= ' root ',     password= ',     charset= "UTF8") prinT db2.insert (SQL) Print db2.insertmany (sql_tmp, values) 

This article is from the "bit accumulation" blog, please be sure to keep this source http://tianxingzhe.blog.51cto.com/3390077/1727000

Python operation MySQL Database