pymysql inserting random numbers into a column using python

Question

I want to insert random numbers to a MySQL column rnd_value with 100 random numbers including and between 1 to 100 using python.

I will generate random numbers from python using

random.randrange(1,100)

I have added MYSQL query to the database

 CREATE SCHEMA `random_values` ;
CREATE TABLE `exercise`.`random_values` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `rnd_value` INT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '');

I use pymysql connector to insert data into MySQL database. Can anyone suggest me to how to insert these random numbers into a MySQL column using python?

`create table blah ( id int auto_increment primary key, theNum int not null );` .... `insert blah(theNum) values(floor(RAND(100)*4)+1);` — Drew, Aug 10 '16 at 04:25
You should use an INSERT SQL command. Did you read about pymysql and MySQL syntax? — Doron Cohen, Aug 10 '16 at 04:25
I'm able to connect MySQL database using pymysql, I'm also able to pass the data to MySQL database, but I want to pass random numbers to column "rnd_value" using python. — Sai Kiran, Aug 10 '16 at 04:33
So basically you don't know how to pass a number from python — Drew, Aug 10 '16 at 04:41
I'm able to pass the number to MySQL from python, I want to fill the column "rnd_value" with 100 rows with random numbers generated from python. — Sai Kiran, Aug 10 '16 at 04:43

James McCormac · Answer 1 · 2016-08-10T04:53:31.343

How about this simple approach? Connect to the db (assuming localhost and database name is exercise). Then shove the 100 values in 1 by 1?

import pymysql
import random

with pymysql.connect(host='localhost', db='exercise') as db:
   for i in random.randrange(1,100):
      qry = "INSERT INTO random_values (rnd_value) VALUES ({})".format(i)
      with db.cursor() as cur:
         cur.execute(qry)
         db.commit()

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

referred some portions from: Pymysql Insert Into not working

install pymysql

pip install pymysql

in mysql

CREATE TABLE `t1` (`c1` INT NULL,`c2` INT NULL )

python program

import pymysql
from random import randint
conn = pymysql.connect(host='localhost', port=3306, user="root")
cursor = conn.cursor()
for i in range (1, 101): # since you want to add 100 entries
  v1 = randint(1,100)
  v2 = randint(1,100)
  sql_statement = "INSERT INTO test.t1(c1, c2) VALUES (" + str(v1) + "," + str(v2)  + ")"
  cursor.execute(sql_statement)
conn.commit() # commit to insert records into database
cursor.close()
conn.close()

for multiple inserts, use some sort of looping mechanism, or use multi insert method

Then switch over to mysql console and fire select statements to see if insert succeeded

edited May 23 '17 at 11:45

Community

1
1

answered Aug 10 '16 at 04:51

Ashwini Kumar

167
8

context managers close open cursors and connections. this is important when multiple users use the same database. closing the cursor and connection after use is advised. – James McCormac Aug 10 '16 at 04:55
sure....updated the answer. I did not assume that in the initial scope of the answer. – Ashwini Kumar Aug 10 '16 at 05:02

score 0 · Accepted Answer · answered Aug 10 '16 at 05:46

Thanks for all the responses, this code seems to work for me,

import pymysql
import random

connection=pymysql.connect(host='localhost',user='root',password='server',
                           db='exercise',charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)


try:
    with connection.cursor() as cursor:
        for i in range (1, 101):
            j=random.randrange(1,100)
            sql = "INSERT INTO exercise.random_values(rnd_value) VALUES (%s)"
            cursor.execute(sql,(j))
            connection.commit()

finally:
    connection.close()

pymysql inserting random numbers into a column using python

3 Answers3

install pymysql

in mysql

python program