import pymongo

sql_pattern = "select * from my_collection where name = %s" #注意，這里直接用%s,不要給%s加引號(hào)，因?yàn)楹竺孓D(zhuǎn)移過后會(huì)自動(dòng)加引號(hào)
name = "xxx\xxx"
name = connection.escape(name)
sql = sql_pattern%name
print(sql) # select * from my_collection where name = 'xxx\\xxx'

with connection.cursor() as cursor:
 try:
  cursor.execute(sql)
 except:
  print(sql)
  pass
 for r in cursor:
  print(r)

# -*- coding: utf-8 -*-
# Author:benjamin

import pymysql

# 創(chuàng)建連接
conn = pymysql.connect(host='192.168.214.128', port=3306, user='root', passwd='ben123', db='db2')

# 創(chuàng)建游標(biāo)
cursor = conn.cursor()

def createTable():
 '''
 創(chuàng)建數(shù)據(jù)庫表
 :return:
 '''
 
 try:
  sql = '''
   create table mytable (
   nid int not null auto_increment primary key,
   name varchar(255) not null,
   email varchar(255) not null,
   extra text
   )engine=innodb default charset=utf8
  '''
  cursor.execute(sql)
  conn.commit()
  print('create table ok!')
 except Exception as e:
  print(e)


def myList(value):
 '''
 生成一個(gè)列表，[('admin1', 'admin1qq.com', 'hahaadmin1'),...]
 :param value: 自定義的數(shù)據(jù)量
 :return: new_list
 '''
 new_list = [] # 新建一個(gè)空列表用來存儲(chǔ)元組數(shù)據(jù)

 for i in range(1, value + 1):

  name = 'admin'+ str(i)
  email = name + '@qq.com'
  extra = 'I am '+ name

  tup = (name,email,extra) # 構(gòu)造元組
  new_list.append(tup) # [(),(),()...]

 print("*"*5+"generate list ok"+"*"*5)
 return new_list


def myInsert(newList):
 '''
 數(shù)據(jù)庫插入
 :param newList: 傳入的列表數(shù)據(jù)
 :return:
 '''

 try:
  sql = "insert into mytable(name,email,extra) values(%s,%s,%s)" # 要插入的數(shù)據(jù)
  cursor.executemany(sql,newList) # 執(zhí)行插入數(shù)據(jù)

  conn.commit()
  cursor.close()
  conn.close()
  print('insert ok')
 except Exception as e:
  print(e)


if __name__ == '__main__':

 # 創(chuàng)建數(shù)據(jù)表
 createTable()
 # 選擇要插入的數(shù)據(jù)量
 value = 1000000 # 定義數(shù)據(jù)量
 newList = myList(value)
 myInsert(newList)