This Banner is For Sale !!
Get your ad here for a week in 20$ only and get upto 15k traffic Daily!!!

Python code that inserts a large number of records into a MySQL table.


This Python code creates a MySQL desk named my_table with columns identify, age, and metropolis.

It then inserts 1 million information into the desk with random knowledge for demonstration functions.

import mysql.connector
import random

# Database configuration
db_config = {
    'host': '127.0.0.1',
    'port': 3309,
    'consumer': 'my_user',
    'password': 'my_password',
    'database': 'my_database'
}

# Operate to create connection and insert information
def insert_records(num_records):
    strive:
        connection = mysql.connector.join(**db_config)
        cursor = connection.cursor()

        for i in vary(num_records):
            # Generate random knowledge for demonstration
            identify = f'Identify{i}'
            age = random.randint(18, 80)
            metropolis = f'Metropolis{i % 100}'  # Solely 100 cities for simplicity

            # Insert report into the desk
            cursor.execute("INSERT INTO my_table (identify, age, metropolis) VALUES (%s, %s, %s)", (identify, age, metropolis))

        connection.commit()
        print(f"{num_records} information inserted efficiently")
    besides mysql.connector.Error as error:
        print("Error inserting information:", error)
    lastly:
        if connection.is_connected():
            cursor.shut()
            connection.shut()

# Variety of information to insert
num_records = 1000000  # Inserting 1 million information

# Create desk if not exists
create_table_query = '''
CREATE TABLE IF NOT EXISTS my_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identify VARCHAR(255),
    age INT,
    metropolis VARCHAR(255)
)
'''

strive:
    connection = mysql.connector.join(**db_config)
    cursor = connection.cursor()
    cursor.execute(create_table_query)
    print("Desk 'my_table' created efficiently")
besides mysql.connector.Error as error:
    print("Error creating desk:", error)
lastly:
    if connection.is_connected():
        cursor.shut()
        connection.shut()

# Insert information
insert_records(num_records)
Enter fullscreen mode

Exit fullscreen mode

Earlier than operating this code, make sure that you outline
‘host’: ‘127.0.0.1’,
‘port’: 3309,
‘consumer’: ‘my_user’,
‘password’: ‘my_password’,
‘database’: ‘my_database’
together with your precise MySQL credentials and database identify.

Additionally, make it possible for the mysql-connector-python package deal is put in

pip set up mysql-connector-python

dmi@dmi-laptop:~/my_mysql_postgres$ pip set up mysql-connector-python
Defaulting to consumer set up as a result of regular site-packages shouldn't be writeable
Gathering mysql-connector-python
  Downloading mysql_connector_python-8.4.0-cp310-cp310-manylinux_2_17_x86_64.whl (19.4 MB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 19.4/19.4 MB 3.8 MB/s eta 0:00:00
Putting in collected packages: mysql-connector-python
Efficiently put in mysql-connector-python-8.4.0
Enter fullscreen mode

Exit fullscreen mode

Instance:

mysql> choose rely(1) from my_table;
+----------+
| rely(1) |
+----------+
|  1000000 |
+----------+
1 row in set (0.05 sec)

mysql> 

mysql> choose * from my_table restrict 10;
+----+-------+------+-------+
| id | identify  | age  | metropolis  |
+----+-------+------+-------+
|  1 | Name0 |   38 | City0 |
|  2 | Name1 |   49 | City1 |
|  3 | Name2 |   27 | City2 |
|  4 | Name3 |   64 | City3 |
|  5 | Name4 |   19 | City4 |
|  6 | Name5 |   63 | City5 |
|  7 | Name6 |   36 | City6 |
|  8 | Name7 |   42 | City7 |
|  9 | Name8 |   51 | City8 |
| 10 | Name9 |   54 | City9 |
+----+-------+------+-------+
10 rows in set (0.01 sec)

mysql> 
Enter fullscreen mode

Exit fullscreen mode

ask_dima@yahoo.com

The Article was Inspired from tech community site.
Contact us if this is inspired from your article and we will give you credit for it for serving the community.

This Banner is For Sale !!
Get your ad here for a week in 20$ only and get upto 10k Tech related traffic daily !!!

Leave a Reply

Your email address will not be published. Required fields are marked *

Want to Contribute to us or want to have 15k+ Audience read your Article ? Or Just want to make a strong Backlink?