Pythonによる乱数を用いたデータInsert処理

by

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

$
Posted in: DB, MYSQL, シェルスクリプト, 運用 ⋅ Tagged: , , ,

Comments are closed.

Post Navigation