I have to use the variable inside the following Linux command in a small python program. But, the only issue is the variable is not getting assigned and getting "variable not substitute error".

import os
var = "DB_NAME.TABLE_NAME"   # If this works, I need to use a list to get the required ddl's  fom the scrikpt.

for i in var:
   cmd ="sed -n '/$i/,${p;/;/q}' DDL_Script.sql >> test_1.txt"
   os.system(cmd)

Note: The sed command does the following task: It will take the lines between two search strings i.e. one is table name and ending with a semicolon.

Updated the question: The ddl_script.sql table looks as below (with nultiple lines). The above pasted sed command in question works in getting the complete ddl for the "db.table_name" passed. But, only issue with the variable replacement if I want to do for multiple tables.

CREATE MULTISET TABLE DB_STG.TABLE_A ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      RUN_ID DECIMAL(10,0),
      ROW_ID DECIMAL(10,0),
      RSN_ID DECIMAL(12,0))
PRIMARY INDEX ( RUN_ID ,ROW_ID );

--

/* <sc-table> DB_STG.TABLE_B </sc-table> */

CREATE SET TABLE DB_STG.TABLE_B ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      POST_DATE DATE FORMAT 'YYYY-MM-DD',
      DEPT_ID INTEGER,
      LOAD_DTE DATE FORMAT 'YYYY-MM-DD')
UNIQUE PRIMARY INDEX TABLE_B_PI ( POST_DATE ,DEPT_ID );```

CodePudding user response：

You can do this without python, just with bash, sed and grep.

1. create a text file that contains the tablename of the subset of tables that you need, one tablenmame per line, say table_subset.txt
2. now run in a bash shell this command:

sed -z "s/--[^\n]*\\n//g; s/\\n//g; s/;/;\\n/g;" ddl_script.sql | grep -F -f table_subset.txt

If you are satisfied with the output: pipe the output into a file and run that file through your sql tool.

• the first sed part does several things:
  • s/--[^\n]*\\n//g; removes the -- line comments
  • s/\\n//g; removes all newlines
  • s/;/;\\n/g; inserts again newlines behind ;
• the result is: each create table command up to and including the final ; is on one line
• those transformed lines are run through the grep and grep uses your subset of tablenames to print only the desired lines

CodePudding user response：

The shell does not perform variable substitutions inside single quotes. But $i is not a shell variable either; you want to use Python to perform the replacement;

for i in var:
   cmd ="sed -n '/%i/,${p;/;/q}' DDL_Script.sql >> test_1.txt" % i
   os.system(cmd)

or perhaps better

with open('test_1.txt', 'a', encoding='utf-8') as output:
    for i in var:
        subprocess.run(['sed', '-n', '/%i/,${p;/;/q}' % i, 'DDL_Script.sql'], stdout=output)

In case it's not obvious, 'string with %i here' % var replaces the placeholder format string %i (where the i is a code letter which means "this is an integer"; similarly %s is a placeholder for a string value, etc) with the value of the numeric variable var in the string, producing 'string with 123 here' if var has the value 123.

Of course, this is still completely harebrained; Python can natively extract lines from a file.

with open('DDL_script.sql', 'r', encoding='utf-8') as ddl, 
     open('test_1.txt', 'a', encoding='utf-8') as output:
   copy_lines = False
   for line in ddl:
       if any(x in line for x in var):
           copy_lines = True
       elif ';' in line:
           copy_lines = False
       if copy_lines:
           output.write(line)

This assumes that var is a list of strings, not a single string.

If Python is not central to your question, doing the same in a single sed script might make more sense. So, for example, if var contains the strings string1, TABLE TWO, and thr333, your script might look like

sed -n '/\(string1\|TABLE TWO\|thr333\)/,/;/p' DDL_Script.sql >> test_1.txt

The semantics here are slightly different from your original script, in that your original script would only fetch the first occurrence of any of the search strings; this will fetch all of them, which of course is harmless if you expect the input file to only contain one match. If not, perhaps clarify.