Блог

У меня есть каталог, полный РЕЗЮМЕ, которые необходимо импортировать в разные таблицы базы данных SQL Server. К счастью, имя файла добавленных CSV начинается со строки «Concat_AAAAA_XX…», где часть AAAAA представляет собой буквенно-цифровую строку, за которой следует XX, представляющее собой двойное целое число. Оба они действуют как ключи для определенной таблицы в SQL.

Мой вопрос в том, какой был бы самый элегантный способ создать скрипт на Python, который бы принимал значения AAAAA и XX из каждого имени файла и знал, в какую таблицу импортировать эти данные?

 CSV1 named: Concat_T101_14_20072021.csv
would need to be imported into Table A

CSV2 named: Concat_RB728_06_25072021.csv
would need to be imported into Table B

CSV3 named: Concat_T144_21_27072021.csv
would need to be imported into Table C

and so on...
 

Я читал, что пакет ConfigParser может помочь, но не могу понять, как применить его теорию здесь. Причина предложения ConfigParser заключается в том, что я хотел бы иметь гибкость или редактировать файл конфигурации (например, «CONFIG.INI») вместо того, чтобы жестко кодировать новые записи в скрипт python.

Код, который у меня есть до сих пор, работает только для отдельного набора данных, который можно найти здесь.

Вот код, который я использую:

 import pypyodbc as odbc
import pandas as pd 
import os

os.chdir('SQL Loader')
df = pd.read_csv('Real-Time_Traffic_Incident_Reports.csv')

df['Published Date'] = pd.to_datetime(df['Published Date']).dt.strftime('%Y-%m-%d %H:%M:%S')
df['Status Date'] = pd.to_datetime(df['Published Date']).dt.strftime('%Y-%m-%d %H:%M:%S')

df.drop(df.query('Location.isnull() | Status.isnull()').index, inplace=True)

columns = ['Traffic Report ID', 'Published Date', 'Issue Reported', 'Location', 
            'Address', 'Status', 'Status Date']

df_data = df[columns]
records = df_data.values.tolist()

DRIVER = 'SQL Server'
SERVER_NAME = 'MY SERVER'
DATABASE_NAME = 'MYDATABASE'

def connection_string(driver, server_name, database_name):
    conn_string = f"""
        DRIVER={{{driver}}};
        SERVER={server_name};
        DATABASE={database_name};
        Trust_Connection=yes;        
    """
    return conn_string

try:
    conn = odbc.connect(connection_string(DRIVER, SERVER_NAME, DATABASE_NAME))
except odbc.DatabaseError as e:
    print('Database Error:')    
    print(str(e.value[1]))
except odbc.Error as e:
    print('Connection Error:')
    print(str(e.value[1]))


sql_insert = '''
    INSERT INTO Austin_Traffic_Incident 
    VALUES (?, ?, ?, ?, ?, ?, ?, GETDATE())
'''

try:
    cursor = conn.cursor()
    cursor.executemany(sql_insert, records)
    cursor.commit();    
except Exception as e:
    cursor.rollback()
    print(str(e[1]))
finally:
    print('Task is complete.')
    cursor.close()
    conn.close()
 

Вы можете создать таблицу перевода, используя dict похожее

 import re
from glob import glob

translation_table = {
    '14': 'A', 
    '06': 'B',
    '21': 'C'
    }

# get all csv files from current directory
for filename in glob("*.csv"):

    # extract the file number with a regular expression
    # (can also be done easily with split function)
    filenum = re.match(r"^Concat_([0-9] )_[0-9]{8}.csv$", filename).group(1)

    # use the translation table to get the table name
    tablename = translation_table[filenum]
    
    print(f"Data from file '{filename}' goes to table '{tablename}'")
 

Я бы сказал, что существует несколько способов сделать что-то подобное. Вы можете использовать чистый SQL, как я проиллюстрирую ниже, или вы можете использовать Python. Если вам нужно решение на Python, просто напишите ответ, и я предоставлю код. Некоторым людям не нравится то, что люди рекомендуют решения за пределами конкретной технологии, которые они перечисляют в исходном посте. Итак, вот решение SQL.

 DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=48)
BEGIN

PRINT @intFlag


declare @fullpath1 varchar(1000)
select @fullpath1 = '''\sourceFTP1'   convert(varchar, getdate()- @intFlag , 112)   '_SPGT.SPL'''
declare @cmd1 nvarchar(1000)
select @cmd1 = 'bulk insert [dbo].[table1] from '   @fullpath1   ' with (FIELDTERMINATOR = ''t'', FIRSTROW = 5, ROWTERMINATOR=''0x0a'')'
exec (@cmd1)

-------------------------------------------

declare @fullpath2 varchar(1000)
select @fullpath2 = '''\sourceFTP2'   convert(varchar, getdate()-@intFlag, 112)   '_SPBMI_GL_PROP_USD_C.SPL'''
declare @cmd2 nvarchar(1000)
select @cmd2 = 'bulk insert [dbo].[table2] from '   @fullpath2   ' with (FIELDTERMINATOR = ''t'', FIRSTROW = 5, ROWTERMINATOR=''0x0a'')'
exec (@cmd2)

-------------------------------------------

declare @fullpath3 varchar(1000)
select @fullpath3 = '''\sourceFTP3'   convert(varchar, getdate()-@intFlag, 112)   '_SPBMI_GL_PROP_USD_C_ADJ.SPC'''
declare @cmd3 nvarchar(1000)
select @cmd3 = 'bulk insert [dbo].[table3] from '   @fullpath3   ' with (FIELDTERMINATOR = ''t'', FIRSTROW = 7, ROWTERMINATOR=''0x0a'')'
exec (@cmd3)

-------------------------------------------

declare @fullpath4 varchar(1000)
select @fullpath4 = '''\sourceFTP4'   convert(varchar, getdate()-@intFlag, 112)   '_SPGTINFRA_ADJ.SPC'''
declare @cmd4 nvarchar(1000)
select @cmd4 = 'bulk insert [dbo].[table4] from '   @fullpath4   ' with (FIELDTERMINATOR = ''t'', FIRSTROW = 7, ROWTERMINATOR=''0x0a'')'
exec (@cmd4)

SET @intFlag = @intFlag   1
    
END
GO
 

Вот решение на Python, о котором вы просили.

Решение на Python, конечно, намного проще.

 import pyodbc

engine = "mssql pyodbc://server_name/db_name?driver=SQL Server Native Client 11.0?trusted_connection=yes"

for f in all_files: 
  # load each file into each dataframe...something like...
  df = pd.read_csv(f, delimiter='t', skiprows=0, header=[0]) 
  # all_df[x].append(df) ... you may or may not need to append ...depends on your setup
  # depends on your setup...
  
  df.to_sql(table_name, engine, if_exists='replace', index=True, chunksize=100000)