欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Python - MySQL数据库相关对象及优化操作

程序员文章站 2024-03-21 17:58:10
...

Python数据库的Connection、Cursor两大对象

Python数据库图解流程
Python - MySQL数据库相关对象及优化操作
Python - MySQL数据库相关对象及优化操作


参数优化

url
在url后加上参数rewriteBatchedStatements=true表示MySQL服务开启批次写入,此参数是批次写入的一个比较重要参数,可明显提升性能
batchsize
DataFrame writer批次写入MySQL的条数,也为提升性能的参数
isolationLevel
事务隔离级别,DataFrame写入不需要开启事务,为None
truncate
overwrite模式时可用,表示在覆盖原始数据的时候不会删除表结构而是复用


Another

Python实现Java中的PrepareStatement功能

# import MySQL module
import MySQLdb
# get user input
name = raw_input("Please enter a name: ")
species = raw_input("Please enter a species: ")
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("INSERT INTO animals (name, species) VALUES (%s, %s)",(name, species))
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("""INSERT INTO test (field1, field2) VALUES ("val1","val2")""")
# get ID of last inserted record
print "ID of inserted record is ", int(cursor.insert_id())
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",db="db56a")
# create a cursor
cursor = db.cursor()
# dynamically generate SQL statements from  list
cursor.executemany("INSERT
INTO animals (name, species) VALUES (%s,%s)", [('Rollo', 'Rat'), 
('Dudley', 'Dolphin'),  ('Mark', 'Marmoset')])
# import MySQL module
import MySQLdb
# initialize some variables
name = ""
data = []
# loop and ask for user input
while (1):
name = raw_input("Please enter a name (EOF to end): ")
if name == "EOF":
break
species = raw_input("Please enter a species: ")
# put user input into a tuple
tuple = (name, species)
# and append to data[] list
data.append(tuple)
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",db="db56a")
# create a cursor
cursor = db.cursor()
# dynamically generate SQL statements from data[] list
cursor.executemany("INSERT INTO animals (name, species) VALUES (%s,%s)",data)