MySQL批量操作with Python

使用Python进行MySQL的增删查改批量操作.

1. insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
import mysql.connector

db = mysql.connector.connect(user='username', password='pwd', host='',database='')
cursor = db.cursor()

totalCount = 0

def insert(ids, cursor):
    # key point
    format_strings = ','.join(['%s'] * len(ids))
    try:
        cursor.executemany("insert INTO orders(USERID,CITYCODE,MOBILENO ) values(%s,%s,%s)", tuple(ids))
        db.commit()
    except Exception as e:
        print e


def readAndExecute():
    f = open("/xxx/users")
    line = f.readline()

    lists = []
    while line:
        strings = line.replace('\r', '').replace('\n', '')#.split("\t")  # .replace('\t', '')
        lists.append(strings.split())

        if len(lists) == 1000:
            insert(lists, cursor)
            lists = []
        line = f.readline()

    if len(lists) != 0:
        insert(lists, cursor)

    f.close()
    db.close()


if '__main__'==__name__:
    readAndExecute()

2. query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
import mysql.connector

db = mysql.connector.connect(user='username', password='passwd',host='hostname',database='dbname')
cursor = db.cursor()


def query(ids, cursor):
    try:
	#Attention: ','join(list)
        format_strings = ','.join(['%s'] * len(ids))
        # print(format_strings)
        cursor.execute("SELECT USERID,orderid FROM orders WHERE userid IN (%s)"% format_strings,tuple(ids))
        results = cursor.fetchall()
        for rows in results:
            print"%s   %s"%(rows[0],rows[1])
        db.commit()
    except Exception as e:
        print ""

def readFileAndExecute():
   #只能同时打开一个文件?
   f = open("/xxx/users")
   line = f.readline()

   lists = []
   while line:
       line = line.replace('\r', '').replace('\n', '').replace('\t', '')
       lists.append(line)
       
	#每次查1000个
       if len(lists) == 1000:
           query(lists,cursor)
           lists=[]
       line = f.readline()

   if len(lists) != 0:
       query(lists, cursor)

   f.close()
   db.close()


if __name__=='__main__':
   readFileAndExecute()

3. update

4. delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
import mysql.connector

db = mysql.connector.connect(user='', password='',host='',database='')
cursor = db.cursor()

def delete(ids, cursor):
    format_strings = ','.join(['%s'] * len(ids))
    
    try:
    	cursor.execute("DELETE FROM order WHERE id IN (%s)" % format_strings,tuple(ids))
        db.commit()
    except Exception as e:
	print e
    print("Operation Sucessfully!")


def readAndExecute():
   f = open("/path/to/iddd.txt")
   line = f.readline()

   lists = []
   while line:
       line = line.replace('\r', '').replace('\n', '').replace('\t', '')
       lists.append(line)

       if len(lists) == 1000:
           delete(lists,cursor)
           lists=[]
       line = f.readline()

   if len(lists) != 0:
       delete(lists, cursor)

   f.close()
   db.close()


if '__main__'==__name__:
    readAndExecute()