Pythonによる、乱数を用いたデータInsert処理確認
事前データ状況確認
mysql> select * from language; +-------------+----------+---------------------+ | language_id | name | last_update | +-------------+----------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | | 7 | Chinese | 2014-08-04 11:36:23 | | 8 | Korean | 2014-08-04 14:20:16 | | 9 | Ainu | 2014-08-04 14:44:03 | | 10 | Irish | 2014-08-04 14:44:03 | +-------------+----------+---------------------+ 10 rows in set (0.00 sec) mysql>
乱数を用いてデータInsertする為に処理を入れる。
但し、データベースのテーブル内容とは関係無い文字列を利用しています。
$ vim mysql_loop.py $ cat mysql_loop.py # coding: utf-8 try: # import import mysql.connector import string from random import randrange # 接続 connect = mysql.connector.connect(user='root', password='password', host='localhost', database='test', charset='utf8') # カーソル cursor = connect.cursor() # SQL 発行 # cursor.execute('select language_id,name from language',()) # Random Charactor Create LENGTH = 20 alphabets = string.digits + string.letters def randstr(n): return ''.join(alphabets[randrange(len(alphabets))] for i in xrange(n)) if __name__ == '__main__': randstr_ins = randstr(LENGTH) insert_stmt = 'insert into language(name) values("%s")' % randstr_ins cursor.execute(insert_stmt) # connect.commit() # フェッチ # # rows = cursor.fetchall() # print(rows) connect.commit() cursor.close() # 切断 connect.close() except Exception as myout: print(myout) $ $ python mysql_loop.py
20文字で作成された乱数文字が入力された事を確認。
mysql> select * from language; +-------------+----------------------+---------------------+ | language_id | name | last_update | +-------------+----------------------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | | 7 | Chinese | 2014-08-04 11:36:23 | | 8 | Korean | 2014-08-04 14:20:16 | | 9 | Ainu | 2014-08-04 14:44:03 | | 10 | Irish | 2014-08-04 14:44:03 | | 11 | bihnbj3Mr4xcRxtaJaDE | 2014-08-04 16:04:56 | +-------------+----------------------+---------------------+ 11 rows in set (0.00 sec) mysql>
上記スクリプトの確認出来たので、データ作成用のLoopが動くか確認。
1) 事前データ確認
mysql> select * from language; +-------------+----------------------+---------------------+ | language_id | name | last_update | +-------------+----------------------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | | 7 | Chinese | 2014-08-04 11:36:23 | | 8 | Korean | 2014-08-04 14:20:16 | | 9 | Ainu | 2014-08-04 14:44:03 | | 10 | Irish | 2014-08-04 14:44:03 | | 11 | bihnbj3Mr4xcRxtaJaDE | 2014-08-04 16:04:56 | | 12 | xadk5JJmgPLkw7kFQ2LT | 2014-08-04 16:10:46 | +-------------+----------------------+---------------------+ 12 rows in set (0.00 sec) mysql>
2) PRINTコマンドで確認
$ vim mysql_loop.py $ python mysql_loop.py insert into language(name) values("t6hiIohEArPADMXA7P35") insert into language(name) values("51akdy18UtKDJVeNXjdK") insert into language(name) values("BiQnfmMX4hlBynVJpXKC") insert into language(name) values("6NdLDWOS72vKy4hfYtgZ") insert into language(name) values("j2WbDYCt63PO5XQtK5Qr") insert into language(name) values("QR05Zd7Y8Y9EnKXFqjMr") insert into language(name) values("JMIF6GKBF7yyHnVUxFc0") insert into language(name) values("ScgelcWkaQVrLKkPkjtV") insert into language(name) values("1bWLI0OMRv6CNEhe8Iax") insert into language(name) values("ZTSCsBNyWqxN32ErJnxE") Finish Creating Data $ vim mysql_loop.py
3) PRINTをコメントして実際のSQLコマンド実行してみる。
$ cat mysql_loop.py # coding: utf-8 try: # import import mysql.connector import string from random import randrange # 接続 connect = mysql.connector.connect(user='root', password='password', host='localhost', database='test', charset='utf8') # カーソル cursor = connect.cursor() # SQL 発行 # cursor.execute('select language_id,name from language',()) for i in range(0, 10): # Random Charactor Create LENGTH = 20 alphabets = string.digits + string.letters def randstr(n): return ''.join(alphabets[randrange(len(alphabets))] for i in xrange(n)) if __name__ == '__main__': randstr_ins = randstr(LENGTH) insert_stmt = 'insert into language(name) values("%s")' % randstr_ins cursor.execute(insert_stmt) #print insert_stmt #connect.commit() else: print('Finish Creating Data') # フェッチ # # rows = cursor.fetchall() # print(rows) connect.commit() cursor.close() # 切断 connect.close() except Exception as myout: print(myout) $ $ python mysql_loop.py Finish Creating Data $
4) Loopされた後にテーブルを確認してみて、テスト用のデータ作成確認
mysql> select * from language; +-------------+----------------------+---------------------+ | language_id | name | last_update | +-------------+----------------------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2014-07-15 16:14:50 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | | 7 | Chinese | 2014-08-04 11:36:23 | | 8 | Korean | 2014-08-04 14:20:16 | | 9 | Ainu | 2014-08-04 14:44:03 | | 10 | Irish | 2014-08-04 14:44:03 | | 11 | bihnbj3Mr4xcRxtaJaDE | 2014-08-04 16:04:56 | | 12 | xadk5JJmgPLkw7kFQ2LT | 2014-08-04 16:10:46 | | 13 | 8yM0B3BgYbOt2KQW141i | 2014-08-04 16:51:28 | | 14 | PKvnobsxLU3dTZZBItn7 | 2014-08-04 16:51:28 | | 15 | aXu9jdW8OWL7ajtkYf4R | 2014-08-04 16:51:28 | | 16 | 7BGNAekeHNeCSCM8kMxS | 2014-08-04 16:51:28 | | 17 | pKwi8LY8HyAKKmEs7Th0 | 2014-08-04 16:51:28 | | 18 | p5MADXZyinC9Yetr51nj | 2014-08-04 16:51:28 | | 19 | P2hBaAK9HbCiVW8gBSPj | 2014-08-04 16:51:28 | | 20 | Igddxz3UzmwEzYacX6iO | 2014-08-04 16:51:28 | | 21 | FslzpwD8WKBbBhOkvTKz | 2014-08-04 16:51:28 | | 22 | yE8AheE4dglD3lcojEfc | 2014-08-04 16:51:28 | +-------------+----------------------+---------------------+ 22 rows in set (0.00 sec) mysql>
こんな感じでrandomで変数を取得する事も出来る。
$ cat randum_import.py # coding: utf-8 import string from random import randrange import random LENGTH = 20 alphabets = string.digits + string.letters def randstr(n): return ''.join(alphabets[randrange(len(alphabets))] for i in xrange(n)) if __name__ == '__main__': print randstr(LENGTH) print random.random() mix_string = randstr(LENGTH) + str(random.random()) print mix_string $ python randum_import.py 2GaN2hELQd7sDe9Hxdt6 0.28266427386 Zxp7xV3cMolzZFaXtB9p0.235414511771 $