-
Notifications
You must be signed in to change notification settings - Fork 10
/
Copy pathsql2csv.1.html
153 lines (151 loc) · 5.71 KB
/
sql2csv.1.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
<!DOCTYPE html>
<html>
<head>
<title>Caltech Library's Digital Library Development Sandbox</title>
<link href='https://fonts.googleapis.com/css?family=Open+Sans' rel='stylesheet' type='text/css'>
<link rel="stylesheet" href="/css/site.css">
</head>
<body>
<header>
<a href="http://library.caltech.edu"><img src="/assets/liblogo.gif" alt="Caltech Library logo"></a>
</header>
<nav>
<ul>
<li><a href="/">Home</a></li>
<li><a href="./">README</a></li>
<li><a href="LICENSE">LICENSE</a></li>
<li><a href="INSTALL.html">INSTALL</a></li>
<li><a href="user-manual.html">User Manual</a></li>
<li><a href="how-to/">Tutorials</a></li>
<li><a href="search.html">Search Docs</a></li>
<li><a href="about.html">About</a></li>
<li><a href="https://github.com/caltechlibrary/datatools">GitHub</a></li>
</ul>
</nav>
<section>
<h1 id="name">NAME</h1>
<p>sql2csv</p>
<h1 id="synopsis">SYNOPSIS</h1>
<p>sql2csv <a href="#options">OPTIONS</a> SQL_STATEMENT</p>
<p>sql2csv <a href="#options">OPTIONS</a> CONFIG_FILE SQL_STATEMENT</p>
<h1 id="description">DESCRIPTION</h1>
<p>sql2csv takes a config file describing a SQL database connection and
output options needed and a SQL statement as the final parameter. The
output of the SQL query is rendered in CSV format to standard out.
sql2csv supports querying MySQL 8, Postgres and SQLite3 databases.</p>
<p>The configuration file is a JSON document with the following key
value pairs.</p>
<dl>
<dt>dsn_url</dt>
<dd>
(string) A data source name in URL form where the “protocol” element
identifies the database resource being accessed (e.g. “sqlite://”,
“mysql://”, “postgres://”). A data source name are rescribed at <a
href="https://go.dev/wiki/SQLInterface"
class="uri">https://go.dev/wiki/SQLInterface</a>. For the specificly
supported datatabase connection strings see <a
href="https://pkg.go.dev/github.com/glebarez/go-sqlite"
class="uri">https://pkg.go.dev/github.com/glebarez/go-sqlite</a>, <a
href="https://pkg.go.dev/github.com/go-sql-driver/mysql#readme-dsn-data-source-name"
class="uri">https://pkg.go.dev/github.com/go-sql-driver/mysql#readme-dsn-data-source-name</a>
and <a href="https://pkg.go.dev/github.com/lib/pq"
class="uri">https://pkg.go.dev/github.com/lib/pq</a>
</dd>
<dt>header_row</dt>
<dd>
(boolean) if true print a header row in the output, false for no header
row output
</dd>
<dt>delimiter</dt>
<dd>
(single character, default is “,”), sets the field delimited used in
output. It can be set to “ for tab separated values.
</dd>
<dt>use_crlf</dt>
<dd>
(boolean, default is false) if set to true to use “” as the line
terminator between rows of output.
</dd>
</dl>
<p>To connect with a database sql2csv relies on a data source name (DSN)
in URL format. In the URL form the URL’s scheme indicates the type of
database you are connecting to (e.g. sqlite, mysql, postgres). The rest
of the DNS has the following form</p>
<pre><code>[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]</code></pre>
<p>For a simple database like SQLite3 a minimal DSN in url form for a
database file “my_database.sqlite3” would look like</p>
<pre><code> sqlite://file:my_database.sqlite3</code></pre>
<p>For MySQL you need to provide more information to connect
(e.g. username, password). In this example the username is “jane.doe”,
password is “something_secret” the database is “my_database”. (this
example assumes that MySQL 8 is running on localhost at the usual
port).</p>
<pre><code> mysql://jane.doe:something_secret@/my_database</code></pre>
<p>Postgres is similar to the MySQL connection string except the
“scheme” is “postgres” instead of “mysql”.</p>
<h1 id="options">OPTIONS</h1>
<dl>
<dt>-help</dt>
<dd>
display help
</dd>
<dt>-version</dt>
<dd>
display version
</dd>
<dt>-license</dt>
<dd>
display license
</dd>
</dl>
<p>A the following options will override a configuration.</p>
<dl>
<dt>-dsn</dt>
<dd>
use the data source name in URL form instead of a JSON configuration
file
</dd>
<dt>-header</dt>
<dd>
use a header row if true, false skip the header row
</dd>
<dt>-delimiter</dt>
<dd>
Set the delimiter to use, default is comma
</dd>
<dt>-use-cdlf</dt>
<dd>
Force the line ending per row to carage return and line feed if true,
false use line feed
</dd>
<dt>-sql FILENAME</dt>
<dd>
Read sql statement from a file instead of the command line.
</dd>
</dl>
<h1 id="examples">EXAMPLES</h1>
<p>Using the “dbcfg.json” configuration file, display ten rows from
table “mytable” in database indicated in “dbcfg.json”.</p>
<div class="sourceCode" id="cb4"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb4-1"><a href="#cb4-1" aria-hidden="true" tabindex="-1"></a> sql2csv dbcfg.json <span class="st">'SELECT * FROM mytable LIMIT 10'</span></span></code></pre></div>
<p>The CSV output is written standard out and can be redirected into a
file if desired.</p>
<pre class="shell"><code> sql2csv dbcfg.json 'SELECT * FROM mytable LIMIT 10' \
>ten-rows.csv</code></pre>
<p>Read SQL from a file and connect to Postgres without SSL you can pass
the <code>-sql</code> and <code>-dsn</code> options.</p>
<pre class="shell"><code>sql2csv \
-dsn "postgres://${USER}@/${DB_NAME}?sslmode=disable" \
-sql query.sql \
>my_data.csv</code></pre>
</section>
<footer>
<span><h1><A href="http://caltech.edu">Caltech</a></h1></span>
<span>© 2023 <a href="https://www.library.caltech.edu/copyright">Caltech library</a></span>
<address>1200 E California Blvd, Mail Code 1-32, Pasadena, CA 91125-3200</address>
<span>Phone: <a href="tel:+1-626-395-3405">(626)395-3405</a></span>
<span><a href="mailto:[email protected]">Email Us</a></span>
<a class="cl-hide" href="sitemap.xml">Site Map</a>
</footer>
</body>
</html>