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
– Drew, Commented Aug 10, 2016 at 4:25
You should use an INSERT SQL command. Did you read about pymysql and MySQL syntax? — Doron Cohen
– Doron Cohen, Commented Aug 10, 2016 at 4: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
– Sai Kiran, Commented Aug 10, 2016 at 4:33
So basically you don't know how to pass a number from python — Drew
– Drew, Commented Aug 10, 2016 at 4: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
– Sai Kiran, Commented Aug 10, 2016 at 4:43

James McCormac · Accepted Answer · 2016-08-10 04:53:31Z

4

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()

edited Aug 10, 2016 at 4:53

answered Aug 10, 2016 at 4:48

James McCormac

1,7053 gold badges14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:45:26Z

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

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.
sure....updated the answer. I did not assume that in the initial scope of the answer.

Sai Kiran · Accepted Answer · 2016-08-10 05:46:21Z

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()

Collectives™ on Stack Overflow

pymysql inserting random numbers into a column using python

3 Answers 3

Comments

install pymysql

in mysql

python program

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

install pymysql

in mysql

python program

2 Comments

Comments

Linked

Related