cursor.execute('SELECT COUNT(*) FROM your_table_name')中your_table_name是个变量 怎么替换？

如果 `your_table_name` 是一个变量，你可以使用参数化查询来构造 SQL 语句，然后将变量传递给参数。以下是一个示例代码：

import pyodbc

# 连接 SQL Server 数据库
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=your_server_name;DATABASE=your_database_name;UID=your_username;PWD=your_password')

# 构造 SQL 语句，使用参数化查询
table_name = 'your_table_name'
sql = 'SELECT COUNT(*) FROM {}'.format(table_name)

# 执行 SQL 语句
cursor = conn.cursor()
cursor.execute(sql)
row = cursor.fetchone()

if row[0] == 0:
    print('表为空')
else:
    print('表不为空')

# 关闭连接
conn.close()

在这个示例代码中，首先将表名赋值给变量 `table_name`，然后使用 `format` 方法将变量插入到 SQL 语句中。这样可以防止 SQL 注入攻击。最后，通过 `execute` 方法执行 SQL 语句，并将结果存储在变量 `row` 中进行判断。

注意，这个示例代码中的表名、服务器名、数据库名、用户名和密码需要根据实际情况进行修改。

相关问题

重写这段代码 CREATE OR REPLACE PROCEDURE ord_dev AS bill_date VARCHAR2(10); v_sql LONG; v_count NUMBER; v_time VARCHAR2(20); v_d VARCHAR2(2); v_m VARCHAR2(2); CURSOR c_tb IS SELECT * FROM b_cur_names; c_row b_cur_names%ROWTYPE; BEGIN EXECUTE IMMEDIATE 'truncate table b_cur_names'; v_sql := 'insert into b_cur_names Select TABLE_NAME from SYS.ALL_TABLES a Where a.owner =upper(''jour1'') And a.table_name Like upper(''om_subscriber_202___'') and Substr(a.table_name,-6) > 202205 order by 1'; COMMIT; EXECUTE IMMEDIATE 'DROP table ord_dev'; ---------------- v_sql := 'insert into ord_dev Select a.order_id,a.done_date, a.cust_id, a.subscriber_ins_id, a.access_num, c.dev_id, c.dev_name, c.corp_org, c.company_name, c.department_code, c.department_name, c.remarks From JOUR1.Om_Subscriber_202301 a Left Join JOUR1.om_order_202301 b On a.order_id = b.order_id Left Join params1.sec_developer c On b.dev_id = c.dev_id Where 1 =2 '; COMMIT; ------------- FOR c_row IN c_tb LOOP v_sql := ' insert into ord_dev Select a.order_id,a.done_date, a.cust_id, a.subscriber_ins_id, a.access_num, c.dev_id, c.dev_name, c.corp_org, c.company_name, c.department_code, c.department_name, c.remarks From JOUR1.Om_Subscriber_' || substr(c_row.table_name, -6) || ' a Left Join JOUR1.om_order_' || substr(c_row.table_name, -6) || ' b On a.order_id = b.order_id Left Join params1.sec_developer c On b.dev_id = c.dev_id Where 1 = 1 And a.access_num Like ''192%'' And a.action = 0'; COMMIT; END LOOP; END;

下面是重写后的优化代码：

CREATE OR REPLACE PROCEDURE ord_dev AS
v_sql VARCHAR2(32767);
v_count NUMBER;
v_time VARCHAR2(20);
v_d VARCHAR2(2);
v_m VARCHAR2(2);

BEGIN
-- Truncate b_cur_names table
EXECUTE IMMEDIATE 'TRUNCATE TABLE b_cur_names';

-- Get the table names matching the criteria
v_sql := 'INSERT INTO b_cur_names
SELECT TABLE_NAME FROM SYS.ALL_TABLES a
WHERE a.owner = UPPER(:owner)
AND a.table_name LIKE UPPER(:table_name_pattern)
AND SUBSTR(a.table_name,-6) > :table_name_suffix
ORDER BY 1';

EXECUTE IMMEDIATE v_sql USING 'jour1', 'OM_SUBSCRIBER_202___', '202205';

-- Drop ord_dev table if exists
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ord_dev';
EXCEPTION
WHEN OTHERS THEN NULL;
END;

-- Create ord_dev table
v_sql := 'CREATE TABLE ord_dev AS
SELECT a.order_id, a.done_date,
a.cust_id,
a.subscriber_ins_id,
a.access_num,
c.dev_id,
c.dev_name,
c.corp_org,
c.company_name,
c.department_code,
c.department_name,
c.remarks
FROM JOUR1.Om_Subscriber_202301 a
LEFT JOIN JOUR1.om_order_202301 b
ON a.order_id = b.order_id
LEFT JOIN params1.sec_developer c
ON b.dev_id = c.dev_id
WHERE 1 = 2';

EXECUTE IMMEDIATE v_sql;

-- Insert data into ord_dev table
FOR c_row IN (SELECT * FROM b_cur_names)
LOOP
v_sql := 'INSERT INTO ord_dev
SELECT a.order_id, a.done_date,
a.cust_id,
a.subscriber_ins_id,
a.access_num,
c.dev_id,
c.dev_name,
c.corp_org,
c.company_name,
c.department_code,
c.department_name,
c.remarks
FROM JOUR1.Om_Subscriber_' || SUBSTR(c_row.table_name, -6) || ' a
LEFT JOIN JOUR1.om_order_' || SUBSTR(c_row.table_name, -6) || ' b
ON a.order_id = b.order_id
LEFT JOIN params1.sec_developer c
ON b.dev_id = c.dev_id
WHERE 1 = 1
AND a.access_num LIKE ''192%''
AND a.action = 0';

EXECUTE IMMEDIATE v_sql;
COMMIT;
END LOOP;

END;
/

重写后的代码进行了以下优化：

1. 使用绑定变量：将动态SQL中的字面量值改为绑定变量，以提高执行计划的复用和安全性。

2. 使用异常处理：在DROP TABLE语句中添加异常处理机制，以处理表不存在的情况，避免抛出异常。

3. 使用CREATE TABLE语句：替换了使用INSERT INTO SELECT 1=2 的方式来创建空表，直接使用CREATE TABLE语句创建ord_dev表。

4. 使用FOR循环优化插入：使用FOR循环遍历b_cur_names表，动态构建INSERT INTO语句，将数据插入ord_dev表。

请根据实际需求进行测试和验证，确保代码逻辑正确并满足预期的功能。

import pymysql import time while True: # 连接源数据库 src_conn = pymysql.connect(host='10.43.64.110', port=3306, user='selectuser', password='Xy@123456', database='messpdb') print("连接源数据库成功") # 连接目标数据库 dst_conn = pymysql.connect(host='10.43.144.231', port=3306, user='root', password='123456', database='czjsc') print("连接目标数据库成功") # 创建源游标对象 src_cursor = src_conn.cursor() # 创建目标游标对象 dst_cursor = dst_conn.cursor() # 编写SQL查询语句 #各牌号烟丝总重量 sql1 = 'select mat_id,material_name ,ROUND (sum(quantity) ,1 ) weight ,unit_id from messpdb.silk_stock a left join messpdb.maindata_material b on a.mat_id =b.ctrl where mat_id >0 group by mat_id,material_name, unit_id' sql2 = "select ROUND (sum(quantity) ,1 ) weight ,unit_id from messpdb.silk_stock a where mat_id >0 group by unit_id" sql3 = "select aa.ids,ROUND (aa.c1/bb.c2 ,3 )*100 from (select 1 as ids,count(box_code) c1 from messpdb.silk_stock a where mat_id >0 group by ids) aa, (select 1 as ids,count(box_code) c2 from messpdb.silk_stock a group by ids)bb where aa.ids=bb.ids" # 执行SQL查询语句 src_cursor.execute(sql1) src_cursor.execute(sql2) src_cursor.execute(sql3) # 获取查询结果 results1 = src_cursor.fetchall() results2 = src_cursor.fetchall() results3 = src_cursor.fetchall() print("查询数据库成功") # 更新数据到目标数据库 for row in results1: mat_id = row[0] material_name = row[1] weight = row[2] unit_id = row[3] # 将数据更新到目标数据库中 update_sql = "update cs_list set material_name=%s, weight=%s, unit_id=%s where mat_id=%s" dst_cursor.execute(update_sql, (material_name, weight, unit_id, mat_id)) print("更新数据成功") # 更新数据到目标数据库 for row in results2: weight = row[0] unit_id = row[1] # 将数据更新到目标数据库中 update_sql = "update cs2_list set weight=%s, unit_id=%s where id=1" dst_cursor.execute(update_sql, (weight, unit_id)) for row in results3: ids = row[0] stock_ratio = row[1] # 将数据更新到目标数据库中 update_sql = "update cs3_list set stock_ratio=%s where id=1" dst_cursor.execute(update_sql, stock_ratio) # 提交事务并关闭连接 dst_conn.commit() dst_cursor.close() src_cursor.close() dst_conn.close() src_conn.close() print("断开数据库连接成功") time.sleep(1) 帮我debug

在这段代码中，有几个问题需要修复：

1. `src_cursor.execute(sql1)`只执行了第一个SQL查询，后面的两个SQL查询也需要执行，可以将三个查询语句合并成一个查询语句，或者分别执行三次查询语句并将结果保存到不同的变量中。

2. 在更新数据到目标数据库时，对于第二个查询结果和第三个查询结果，更新语句中的where条件都是固定的，这样会导致每次更新都是更新同一行的数据，需要修改where条件。

3. 在更新数据到目标数据库之前，需要先将目标游标对象dst_cursor设置为可接受多条SQL语句，如下所示：

dst_cursor = dst_conn.cursor(pymysql.cursors.DictCursor)

4. 在更新数据到目标数据库之前，需要先将目标表中的数据清空，可以使用如下语句：

truncate_sql = "truncate table cs_list"
dst_cursor.execute(truncate_sql)

综上所述，修改后的代码如下所示：

import pymysql 
import time 

while True: 
    # 连接源数据库 
    src_conn = pymysql.connect(host='10.43.64.110', port=3306, user='selectuser', password='Xy@123456', database='messpdb') 
    print("连接源数据库成功") 
    # 连接目标数据库 
    dst_conn = pymysql.connect(host='10.43.144.231', port=3306, user='root', password='123456', database='czjsc') 
    print("连接目标数据库成功") 
    # 创建源游标对象 
    src_cursor = src_conn.cursor() 
    # 创建目标游标对象 
    dst_cursor = dst_conn.cursor(pymysql.cursors.DictCursor) 
    # 清空目标表中的数据
    truncate_sql = "truncate table cs_list"
    dst_cursor.execute(truncate_sql)
    # 编写SQL查询语句 
    sql = '''
    select a.mat_id, b.material_name, ROUND(sum(a.quantity), 1) as weight, a.unit_id 
    from messpdb.silk_stock a 
    left join messpdb.maindata_material b on a.mat_id = b.ctrl 
    where a.mat_id > 0 
    group by a.mat_id, b.material_name, a.unit_id;

    select ROUND(sum(a.quantity), 1) as weight, a.unit_id 
    from messpdb.silk_stock a 
    where a.mat_id > 0 
    group by a.unit_id;

    select aa.ids, ROUND(aa.c1 / bb.c2, 3) * 100 as stock_ratio 
    from (
        select 1 as ids, count(box_code) as c1 
        from messpdb.silk_stock a 
        where a.mat_id > 0 
        group by ids
    ) aa, (
        select 1 as ids, count(box_code) as c2 
        from messpdb.silk_stock a 
        group by ids
    ) bb 
    where aa.ids = bb.ids;
    '''
    # 执行SQL查询语句 
    src_cursor.execute(sql, multi=True)
    # 获取查询结果 
    results = src_cursor.fetchall() 
    print("查询数据库成功") 
    # 更新数据到目标数据库 
    for row in results[0]: 
        mat_id = row["mat_id"] 
        material_name = row["material_name"] 
        weight = row["weight"] 
        unit_id = row["unit_id"] 
        # 将数据更新到目标数据库中 
        update_sql = "insert into cs_list (mat_id, material_name, weight, unit_id) values (%s, %s, %s, %s)" 
        dst_cursor.execute(update_sql, (mat_id, material_name, weight, unit_id)) 
    for row in results[1]: 
        weight = row["weight"] 
        unit_id = row["unit_id"] 
        # 将数据更新到目标数据库中 
        update_sql = "update cs2_list set weight=%s, unit_id=%s where id=1" 
        dst_cursor.execute(update_sql, (weight, unit_id)) 
    for row in results[2]: 
        ids = row["ids"] 
        stock_ratio = row["stock_ratio"] 
        # 将数据更新到目标数据库中 
        update_sql = "update cs3_list set stock_ratio=%s where id=1" 
        dst_cursor.execute(update_sql, stock_ratio) 
    # 提交事务并关闭连接 
    dst_conn.commit() 
    dst_cursor.close() 
    src_cursor.close() 
    dst_conn.close() 
    src_conn.close() 
    print("断开数据库连接成功") 
    time.sleep(1)