python 操作mysql

1.引入 pymysql包

import pymysql

2.建立连接

conn = pymysql.connect(
    host='********.com',
    user='lionmile',
    password='***********',
    database='lionmile'
    )

3.创建游标

cursor = conn.cursor()

4.执行sql

insterSql = """INSERT INTO tc.reconciliation_payable_detail (reconciliation_id,validity,customer_dimension,project_dimension,
  transport_remark,bill_classification,payment_company,short_invoice_header,`type`,classification,
  self_pickup_is_delivery,express,warehouse_code,tms_reconciliation_id,receipt_no,receipt_status,
  reconciliation_status,waybill_no,cost_categories,shipping_type,external_no,external_waybill_no,
  external_waybill_no_md5,so,customer_name,actual_company_name,delivery_no,status,invoice_header,
  carrier_name,transportation_method,reconciliation_by,sales_person,sales_department,shipping_order_source,
  allocate_time,pickup_time,signed_time,statement_type,send_name,send_province_name,send_city_name,
  send_address,send_linkman,send_mobile,receive_name,receive_province_name,receive_city_name,
  receive_address,receive_linkman,receive_mobile,net_weight_kg,weight_volume,
  kilometers,goods_total_value,package_total,equipment_type,driver_name,equipment_no,carrier_weight,carrier_volume
  ,chargeable_weight,basic_freight,delivery_fee,receipt_fee,insurance_fee,packaging_material_fee,manual_handling_fee,
  vehicle_deposit_fee,mechanical_handling_fee,waiting_fee,special_car_fee,location_fee,other_fee,other_fee_explain,total_fee,advance_payment_fee,
  including_service_fee,tax,untaxed_total,taxed_total,dispatch_plan_no,created_at,updated_at,create_by,create_by_name,update_by,update_by_name,deleted_at) VALUES
(1733,'正常','***','***','****','线上专车','狮行智慧','上海外服','狮行物流-配送站','狮行物流-配送站','是','狮行物流','DBA','',%s,1,1,%s,'西域发货','普通出库 ','SP2404020000136','SP2404020000136','353bb71eec7f9f84eb2d37e5145c8859','SO171012319076876862','国能易购（北京）科技有限公司','','','到货','上海外服贤益人力资源管理有限公司','西域直送','西域直送','admin','','','10',NULL,'2024-04-02 17:43:35','2024-04-02 17:44:04','','神东配送站','陕西省','榆林市','陕西省榆林市神木市大柳塔镇李家畔村一组建材区域7#8#','乔鑫','13098274701','刘晶','陕西省','榆林市','陕西省榆林市府谷县陕西省榆林市府谷县庙沟门镇陕西榆林市府谷县庙沟门镇陕西德源府谷能源有限公司物资库房','刘晶','15191283680',500.000000,0.025920,0.00,0.00,1080,'3.6M专车','冯景辉(13154029768)','陕kP899U',0.000000,0.000000,500.000000,1234.000000,500.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,-500.000000,'3.6，冯景辉，府谷沟门，500元',1234.000000,37.020000,1271.020000,0.06,1234.000000,1347.281200,'OR240402000081',%s,%s,0,'',0,'',NULL)"""

#通过%s可以实现参数化
cursor.execute(insterSql,(pm,sp,time1,time1))

4.更新或插入操作需要commit

conn.commit()

sql查询结果

result = cursor.fetchall()

5.关闭链接
conn.close()