Project

General

Profile

1
#!/usr/bin/env python
2
# Makes PostgreSQL CREATE TABLE and COPY FROM statements from a CSV header
3

    
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
    schema = opts.get_env_var('schema', None, env_names)
26
    table = opts.get_env_var('table', None, env_names)
27
    if schema == None or table == None: usage_err()
28
    
29
    # Get format info
30
    info = csvs.stream_info(sys.stdin, parse_header=True)
31
    dialect = info.dialect
32
    
33
    out = sys.stdout
34
    qual_table = esc_name(schema)+'.'+esc_name(table)
35
    
36
    # Write CREATE TABLE statement
37
    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
    out.write(');\n')
42
    
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

    
55
main()
(7-7/45)