Revision 1925
Added by Aaron Marcuse-Kubitza over 12 years ago
| csv2ddl | ||
|---|---|---|
| 1 | 1 |
#!/usr/bin/env python |
| 2 |
# Makes a PostgreSQL CREATE TABLE statement from a CSV header
|
|
| 2 |
# Makes PostgreSQL CREATE TABLE and COPY FROM statements from a CSV header
|
|
| 3 | 3 |
|
| 4 | 4 |
import csv |
| 5 | 5 |
import os.path |
| ... | ... | |
| 26 | 26 |
table = opts.get_env_var('table', None, env_names)
|
| 27 | 27 |
if schema == None or table == None: usage_err() |
| 28 | 28 |
|
| 29 |
# Get col names |
|
| 30 |
reader, col_names = csvs.reader_and_header(sys.stdin) |
|
| 29 |
# Get format info |
|
| 30 |
info = csvs.stream_info(sys.stdin, parse_header=True) |
|
| 31 |
dialect = info.dialect |
|
| 31 | 32 |
|
| 33 |
out = sys.stdout |
|
| 34 |
qual_table = esc_name(schema)+'.'+esc_name(table) |
|
| 35 |
|
|
| 32 | 36 |
# Write CREATE TABLE statement |
| 33 |
out = sys.stdout
|
|
| 34 |
out.write('CREATE TABLE '+esc_name(schema)+'.'+esc_name(table)+' (\n')
|
|
| 35 |
out.write('row_num serial NOT NULL\n')
|
|
| 36 |
for col_name in col_names: out.write(', '+esc_name(col_name)+' text\n')
|
|
| 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')
|
|
| 37 | 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) |
|
| 38 | 54 |
|
| 39 | 55 |
main() |
Also available in: Unified diff
csv2ddl: Also print a COPY FROM statement