将大量记录插入 MySQL 表的 Python 代码。

此 Python 代码创建一个名为 my_table 的 MySQL 表，其中包含名称、年龄和城市列。

然后将 100 万条记录插入带有随机数据的表中以进行演示。

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)

运行此代码之前，请确保定义
‘主机’：‘127.0.0.1’，
‘端口’：3309，
‘用户’：‘我的用户’，
‘密码’：‘我的密码’，
‘数据库’：‘我的数据库’
使用您的实际 MySQL 凭据和数据库名称。

另外，请确保安装了 mysql-connector-python 软件包

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

示例：

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

以上是将大量记录插入 MySQL 表的 Python 代码。的详细内容。更多信息请关注PHP中文网其他相关文章！