读取csv文件 把数据插入数据库中 

 1 import pandas as pd
 2 import psycopg2
 3 from datetime import datetime
 4 import re
 5 
 6 # 读取CSV文件
 7 csv_file = '文件地址'
 8 df = pd.read_csv(csv_file, sep='|')
 9 
10 # 连接到PostgreSQL数据库
11 conn = psycopg2.connect(
12     dbname="数据库",
13     user="用户",
14     password="密码",
15     host="host",
16     port="端口号"
17 )
18 
19 # 创建一个游标对象
20 cur = conn.cursor()
21 
22 # 查询数据库中的最大ID
23 cur.execute("SELECT COUNT(*) FROM tbt_cardbin")
24 max_id = cur.fetchone()[0] + 1
25 
26 # 执行查询
27 cur.execute("SELECT DISTINCT card_brand FROM tbt_cardbin")
28 # 获取查询结果
29 results = cur.fetchall()
30 # 获取brand
31 first_word = re.search(r'\\([^\\]+)_', csv_file).group(1).split('_')[0]
32 # 遍历查询结果，如果值等于first_word，则更新is_active为N
33 for result in results:
34     if result[0] == first_word:
35         cur.execute(f"UPDATE tbt_cardbin SET is_actived = 'N' WHERE card_brand = '{result[0]}'")
36 
37 # 遍历DataFrame的每一行，插入到数据库中
38 for index, row in df.iterrows():
39     # 生成新的ID 格式是cdno 加8位数字
40     new_id = f"CDNO{str(max_id).zfill(8)}"
41     max_id += 1
42 
43     # 获取当前时间作为load_time字段的值
44     load_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
45     is_actived = "Y"
46 
47     if 'mastercard' in csv_file:
48         ica_bin = 'ICA'
49     else:
50         ica_bin = 'ISSUER_BIN'
51     # 构建插入语句，根据你的表结构和CSV文件内容进行调整
52 #mastercard-ICA  Visa-ISSUER_BIN
53     insert_query = f"""
54     INSERT INTO public.tbt_cardbin (card_no, company_name, ica_bin,account_range_from,account_range_to,
55     brand_product_code,brand_product_name,acceptance_brand,country,card_brand,is_actived,load_time)
56     VALUES (
57     '{new_id}','{row['COMPANY_NAME']}', '{row['ISSUER_BIN']}','{row['ACCOUNT_RANGE_FROM']}','{row['ACCOUNT_RANGE_TO']}',
58     '{row['BRAND_PRODUCT_CODE']}','{row['BRAND_PRODUCT_NAME']}','{row['ACCEPTANCE_BRAND']}','{row['COUNTRY']}','{first_word}',
59     '{is_actived}', '{load_time}');
60     """
61 
62     # 执行插入语句
63     cur.execute(insert_query)
64 
65 # 提交事务
66 conn.commit()
67 
68 # 关闭游标和连接
69 cur.close()
70 conn.close()

 

posted @ 2024-09-05 10:34  末叶da  阅读(16)  评论(0)    收藏  举报