| 1 |
1920
|
aaronmk
|
#!/usr/bin/env python
|
| 2 |
1925
|
aaronmk
|
# Makes PostgreSQL CREATE TABLE and COPY FROM statements from a CSV header
|
| 3 |
1920
|
aaronmk
|
|
| 4 |
|
|
import csv
|
| 5 |
|
|
import os.path
|
| 6 |
|
|
import sys
|
| 7 |
|
|
|
| 8 |
|
|
sys.path.append(os.path.dirname(__file__)+"/../lib")
|
| 9 |
|
|
|
| 10 |
|
|
import csvs
|
| 11 |
|
|
import opts
|
| 12 |
|
|
import sql
|
| 13 |
|
|
|
| 14 |
|
|
def esc_name(name):
|
| 15 |
|
|
return sql.esc_name_by_engine('PostgreSQL', name, preserve_case=True)
|
| 16 |
|
|
|
| 17 |
|
|
def main():
|
| 18 |
|
|
# Usage
|
| 19 |
|
|
env_names = []
|
| 20 |
|
|
def usage_err():
|
| 21 |
|
|
raise SystemExit('Usage: '+opts.env_usage(env_names)+' '+sys.argv[0]
|
| 22 |
|
|
+' <header >ddl')
|
| 23 |
|
|
|
| 24 |
|
|
# Get config from env vars
|
| 25 |
1922
|
aaronmk
|
schema = opts.get_env_var('schema', None, env_names)
|
| 26 |
1920
|
aaronmk
|
table = opts.get_env_var('table', None, env_names)
|
| 27 |
1922
|
aaronmk
|
if schema == None or table == None: usage_err()
|
| 28 |
1920
|
aaronmk
|
|
| 29 |
1925
|
aaronmk
|
# Get format info
|
| 30 |
|
|
info = csvs.stream_info(sys.stdin, parse_header=True)
|
| 31 |
|
|
dialect = info.dialect
|
| 32 |
1920
|
aaronmk
|
|
| 33 |
1925
|
aaronmk
|
out = sys.stdout
|
| 34 |
|
|
qual_table = esc_name(schema)+'.'+esc_name(table)
|
| 35 |
|
|
|
| 36 |
1920
|
aaronmk
|
# Write CREATE TABLE statement
|
| 37 |
1925
|
aaronmk
|
out.write('CREATE TABLE '+qual_table+' (\n')
|
| 38 |
|
|
out.write(' row_num serial NOT NULL\n')
|
| 39 |
|
|
for col_name in info.header:
|
| 40 |
|
|
out.write(' , '+esc_name(col_name)+' text\n')
|
| 41 |
1920
|
aaronmk
|
out.write(');\n')
|
| 42 |
1925
|
aaronmk
|
|
| 43 |
|
|
# Write COPY FROM statement
|
| 44 |
|
|
statement = ('COPY '+qual_table+" FROM STDIN DELIMITER '" +dialect.delimiter
|
| 45 |
|
|
+"' NULL E'\N'")
|
| 46 |
|
|
if not csvs.is_tsv(dialect):
|
| 47 |
|
|
statement += ' CSV'
|
| 48 |
|
|
if dialect.quoting != csv.QUOTE_NONE:
|
| 49 |
|
|
statement += " QUOTE '"+dialect.quotechar+"'"
|
| 50 |
|
|
if dialect.doublequote:
|
| 51 |
|
|
statement += " ESCAPE '"+dialect.quotechar+"'"
|
| 52 |
|
|
statement += ';\n'
|
| 53 |
|
|
out.write(statement)
|
| 54 |
1920
|
aaronmk
|
|
| 55 |
|
|
main()
|
