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

This Python code creates a MySQL table named my_table with columns name, age, and city.

It then inserts 1 million records into the table with random data for demonstration purposes.

import mysql.connector
import random

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

# Function to create connection and insert records
def insert_records(num_records):
    try:
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()

        for i in range(num_records):
            # Generate random data for demonstration
            name = f'Name{i}'
            age = random.randint(18, 80)
            city = f'City{i % 100}'  # Only 100 cities for simplicity

            # Insert record into the table
            cursor.execute("INSERT INTO my_table (name, age, city) VALUES (%s, %s, %s)", (name, age, city))

        connection.commit()
        print(f"{num_records} records inserted successfully")
    except mysql.connector.Error as error:
        print("Error inserting records:", error)
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# Number of records to insert
num_records = 1000000  # Inserting 1 million records

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

try:
    connection = mysql.connector.connect(**db_config)
    cursor = connection.cursor()
    cursor.execute(create_table_query)
    print("Table 'my_table' created successfully")
except mysql.connector.Error as error:
    print("Error creating table:", error)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()

# Insert records
insert_records(num_records)

Before running this code, ensure that you define
‘host’: ‘127.0.0.1’,
‘port’: 3309,
‘user’: ‘my_user’,
‘password’: ‘my_password’,
‘database’: ‘my_database’
with your actual MySQL credentials and database name.

Also, make sure that the mysql-connector-python package is installed

pip install mysql-connector-python

dmi@dmi-laptop:~/my_mysql_postgres$ pip install mysql-connector-python
Defaulting to user installation because normal site-packages is not writeable
Collecting 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
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.4.0

Example:

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

mysql> 

mysql> select * from my_table limit 10;
+----+-------+------+-------+
| id | name  | age  | city  |
+----+-------+------+-------+
|  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> 

ask_dima@yahoo.com

The above is the detailed content of Python code that inserts a large number of records into a MySQL table.. For more information, please follow other related articles on the PHP Chinese website!