使用Navicat建立的表如何通过Python读取和写入？

在Navicat中创建好MySQL表后，可以使用Python连接并操作该表。这里有三种常用的方法：

1. **直接使用pymysql**[^1]:

   import pymysql
   conn = pymysql.connect(host='localhost', user='username', password='password', db='your_database')
   cursor = conn.cursor()
   cursor.execute('SELECT * FROM your_table')  # 读取数据
   rows = cursor.fetchall()
   for row in rows:
       print(row)

   # 插入新数据
   sql = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
   cursor.execute(sql, ('value1', 'value2'))
   conn.commit()  # 提交事务
   cursor.close()
   conn.close()

2. **使用pandas DataFrame与to_sql函数**:

   import pandas as pd
   data = {'column1': ['value1', 'value2'], 'column2': ['value3', 'value4']}
   df = pd.DataFrame(data)
   
   df.to_sql(name='your_table', con=conn, if_exists='append', index=False)

3. **利用SQLAlchemy ORM（对象关系映射）**:

   from sqlalchemy import create_engine, Table, MetaData
   engine = create_engine('mysql+mysqlconnector://user:password@localhost/your_database')
   metadata = MetaData(engine)
   table = Table('your_table', metadata, autoload_with=engine)

   # 读取数据
   query = table.select()
   result = engine.execute(query)
   records = result.fetchall()

   # 插入数据
   new_data = {'column1': 'value1', 'column2': 'value2'}
   insert_statement = table.insert().values(new_data)
   with engine.begin() as connection:
       connection.execute(insert_statement)