Adding New Fields to a Table

Use ALTER TABLE with ADD COLUMN (or simplified ADD) to append new fields. You can specify optional default values to handle existing records.

alter_stmt = '''
    ALTER TABLE automotive_dealer_locations
    ADD COLUMN (legal_business_name VARCHAR(120) DEFAULT NULL,
                paid_in_capital VARCHAR(120) DEFAULT NULL,
                founding_date VARCHAR(120) DEFAULT NULL,
                license_approval_date VARCHAR(120) DEFAULT NULL,
                industry_classification_code VARCHAR(120) DEFAULT NULL,
                business_status VARCHAR(120) DEFAULT NULL,
                unified_social_credit_code VARCHAR(120) DEFAULT NULL,
                org_code VARCHAR(120) DEFAULT NULL,
                actual_capital VARCHAR(120) DEFAULT NULL,
                employee_count VARCHAR(120) DEFAULT NULL,
                social_security_enrolled_count VARCHAR(120) DEFAULT NULL,
                legal_entity_type VARCHAR(120) DEFAULT NULL,
                operation_term VARCHAR(120) DEFAULT NULL,
                core_industry VARCHAR(120) DEFAULT NULL,
                physical_address VARCHAR(120) DEFAULT NULL,
                business_scope VARCHAR(120) DEFAULT NULL)
'''

Updating Existing Data

Use UPDATE with SET to modify specific field values. Always include a WHERE clause to target records precisely and avoid accidental bulk overwrites.

shop_update_stmt = """
    UPDATE auto_service_outlets
    SET legal_business_name = '{}',
        paid_in_capital = '{}',
        industry_classification_code = '{}',
        business_status = '{}',
        unified_social_credit_code = '{}',
        org_code = '{}',
        actual_capital = '{}',
        employee_count = '{}',
        social_security_enrolled_count = '{}',
        legal_entity_type = '{}',
        core_industry = '{}',
        physical_address = '{}',
        business_scope = '{}'
    WHERE unique_outlet_id = '{}'
""".format(legal_name, capital, industry_code, status, uscc, org_code, real_cap, emp_size, ss_count, entity_type, industry, address, scope, outlet_id)

Inserting New Records

Use INSERT INTO to add single or batch records. Parameterized placeholders (%s) are recommended over string formatting to prevent SQL injection.

insert_stmt = '''
    INSERT INTO automotive_dealer_locations(
        city,
        administrative_district,
        registered_company_name,
        main_service_type,
        contact_phone,
        detailed_address
    ) VALUES (%s, %s, %s, %s, %s, %s)
'''

Fetching Column Values from a Database

Use Python’s pymysql library to connect, execute queries, and retrieve results with methods like fetchmany() for paginated data.

import pymysql

# Establish database connection
connection = pymysql.connect(
    host='********',
    user='system_admin',
    password='********',
    port=3306,
    db='automotive_data_hub'
)

# Create cursor object
cursor = connection.cursor()

# Execute SELECT query
cursor.execute('SELECT registered_company_name, unique_dealer_id FROM automotive_dealer_locations')

# Fetch first 50 results
page_results = cursor.fetchmany(50)

# Iterate and print specific column values
for row in page_results:
    print(row[0])  # registered_company_name
    print(row)    # full tuple
    print(row[1])  # unique_dealer_id