SaltyCrane: sqlhttps://www.saltycrane.com/blog/2019-01-09T22:47:12-08:00How to run PostgreSQL in Docker on Mac (for local development)
2019-01-09T22:47:12-08:00https://www.saltycrane.com/blog/2019/01/how-run-postgresql-docker-mac-local-development/<p>
These are my notes for running Postgres in a Docker container for use with a
local Django or Rails development server running on the host machine (not in
Docker). Running in Docker allows keeping my database environment isolated
from the rest of my system and allows running multiple versions and instances.
(I previously had a problem where Homebrew upgraded Postgres when I didn't
expect it to and my existing database became incompatible. Admittedly, I
didn't know Homebrew well, but it was frustrating.) Disadvantages of Docker
are it's another layer of abstraction to learn and interact with. We use
Docker extensively at work, so from a mental overhead point of view, it's
something I wanted to learn anyways. Currently I use the Homebrew Postgres for
work, and Postgres in Docker for personal projects. I also wrote some
<a href="/blog/2017/07/postgres-mac-os-homebrew-notes/"
>notes on Postgres and Homebrew here</a
>.
</p>
<h4 id="install-docker">Install Docker</h4>
<p>
Install Docker for Mac:
<a href="https://docs.docker.com/docker-for-mac/install/"
>https://docs.docker.com/docker-for-mac/install/</a
>.
</p>
<p>
Alternatively, you can install Docker using Homebrew:
<code>brew install homebrew/cask/docker</code>
</p>
<h4 id="using-single-docker-command">
OPTION 1: Run Postgres using a single Docker command
</h4>
<b>Run a postgres container</b>
<ul>
<li>
uses the
<a href="https://hub.docker.com/_/postgres/"
>official docker postgres 13 image</a
>
</li>
<li>
uses a <a href="https://docs.docker.com/storage/volumes/">named volume</a>,
<code>my_dbdata</code>, to store postgres data
</li>
<li>
exposes port 54320 to the host using
<a
href="https://github.com/docker/cli/blob/master/docs/reference/commandline/run.md#publish-or-expose-port--p---expose"
><code>-p</code></a
>
</li>
<li>sets the container name to <code>my_postgres</code></li>
<li>uses the <code>-d</code> flag to run in the background</li>
<li>
sets the postgres superuser password to <code>"my_password"</code> using
<a
href="https://github.com/docker/cli/blob/master/docs/reference/commandline/run.md#set-environment-variables--e---env---env-file"
><code>-e</code></a
>
and the
<a
href="https://github.com/docker-library/docs/blob/master/postgres/README.md#postgres_password"
><code>POSTGRES_PASSWORD</code> environment variable</a
>.
</li>
</ul>
<pre class="console">
$ docker run -d --name my_postgres -v my_dbdata:/var/lib/postgresql/data -p 54320:5432 -e POSTGRES_PASSWORD=my_password postgres:13
</pre>
<h4 id="using-docker-compose">OPTION 2: Run Postgres using Docker Compose</h4>
<b>Create a <code>docker-compose.yml</code> file</b>
<pre class="console">
$ mkdir /tmp/myproject
$ cd /tmp/myproject
</pre>
<p>Create a new file <code>docker-compose.yml</code>:</p>
<pre>
version: "3"
services:
db:
image: "postgres:13"
container_name: "my_postgres"
environment:
POSTGRES_PASSWORD: "my_password"
ports:
- "54320:5432"
volumes:
- my_dbdata:/var/lib/postgresql/data
volumes:
my_dbdata:
</pre>
<ul>
<li>uses docker compose file version 3</li>
<li>
sets up a service named <code>"db"</code> (this name can be used with
<code>docker-compose</code> commands)
</li>
<li>
uses the
<a href="https://hub.docker.com/_/postgres/"
><code>postgres:13</code> image from hub.docker.com</a
>
using the
<a
href="https://docs.docker.com/compose/compose-file/compose-file-v3/#image"
><code>image</code> key</a
>
</li>
<li>
creates a container named <code>"my_postgres"</code> using the
<a
href="https://docs.docker.com/compose/compose-file/compose-file-v3/#container_name"
><code>container_name</code> key</a
>
</li>
<li>
sets the postgres superuser password to <code>"my_password"</code> using the
<a
href="https://docs.docker.com/compose/compose-file/compose-file-v3/#environment"
><code>environment</code> key</a
>
and the
<a
href="https://github.com/docker-library/docs/blob/master/postgres/README.md#postgres_password"
><code>POSTGRES_PASSWORD</code> environment variable</a
>
</li>
<li>
connects port 5432 inside Docker as port 54320 on the host machine using the
<a
href="https://docs.docker.com/compose/compose-file/compose-file-v3/#ports"
><code>ports</code> key</a
>
</li>
<li>
uses a named volume, <code>"my_dbdata"</code>, for storing the database data
using the
<a
href="https://docs.docker.com/compose/compose-file/compose-file-v3/#volumes"
><code>volumes</code> key</a
>. Even if the container and image are deleted, the volume will remain
unless explicitly deleted using <code>docker volume rm</code>.
</li>
<li>
for more information, see the
<a href="https://docs.docker.com/compose/compose-file/"
>Docker Compose file reference</a
>
</li>
</ul>
<b>Start Postgres</b>
<p>
Pull the postgres image from hub.docker.com, create a container named
"my_postgres", and start it in the background:
</p>
<pre class="console">
$ docker-compose up -d
</pre>
<h4 id="see-working">See that it's working</h4>
<p>See the logs:</p>
<pre class="console">
$ docker logs -f my_postgres
</pre>
<p>Try running psql:</p>
<pre class="console">
$ docker exec -it my_postgres psql -U postgres
</pre>
<p>hit CTRL+D to exit</p>
<p>
For other commands such as starting, stopping, listing or deleting, see my
<a href="/cheat-sheets/docker/">Docker cheat sheet</a>.
</p>
<h4 id="create-database">Create a database</h4>
<pre class="console">
$ docker exec -it my_postgres psql -U postgres -c "create database my_database"
</pre>
<h4 id="connect-using-python">
Connect using Python and <code>psycopg2</code>
</h4>
<pre class="console">
$ python3 -m venv myenv
$ source myenv/bin/activate
$ pip install psycopg2-binary
</pre>
<p>Create a new file named <code>myscript.py</code></p>
<pre class="python">
import psycopg2
conn = psycopg2.connect(
host='localhost',
port=54320,
dbname='my_database',
user='postgres',
password='my_password',
)
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS test (id serial PRIMARY KEY, num integer, data varchar);")
cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", (100, "abcdef"))
cur.execute("SELECT * FROM test;")
result = cur.fetchone()
print(result)
conn.commit()
cur.close()
conn.close()
</pre>
<p>Run it</p>
<pre class="console">
$ python myscript.py
(1, 100, 'abcdef')
</pre>
<h4 id="errors">Errors</h4>
<ul>
<li>
<pre><code>docker: Error response from daemon: Conflict. The container name "/my_postgres" is already in use by container "b27594a414db369ec4876a07021c9ea738a55b3bc0a3ad5117158367131b99a2". You have to remove (or rename) that container to be able to reuse that name.</code></pre>
<p>If you get the above error, you can remove the container by running:</p>
<pre class="console">$ docker rm my_postgres</pre>
</li>
<li>
<pre><code>Error response from daemon: You cannot remove a running container 7e94d205b6f4ef40ff885987f11e825e94eddbcd061481e591e07c87ed7cf86e. Stop the container before attempting removal or force remove</code></pre>
<p>If you get the above error, you can stop the container by running:</p>
<pre class="console">$ docker stop my_postgres</pre>
</li>
</ul>
<h4 id="see-also">See also</h4>
<ul>
<li>
<a href="/blog/2017/07/postgres-mac-os-homebrew-notes/">
My Postgres Homebrew notes
</a>
</li>
<li>
<a href="https://www.saltycrane.com/blog/2017/08/docker-cheat-sheet/"
>My Docker cheat sheet</a
>
</li>
<li>
<a href="https://hub.docker.com/_/postgres/"
>postgres image on Docker Hub</a
>
</li>
<li>
<a href="https://docs.docker.com/engine/reference/commandline/run/">
<code>docker run</code> command reference
</a>
</li>
<li>
<a href="https://docs.docker.com/compose/compose-file/">
Docker Compose file reference
</a>
</li>
<li>
<a href="https://docs.docker.com/storage/volumes/"
>Documentation on using volumes</a
>
</li>
</ul>
postgres on Mac OS (homebrew) notes
2017-07-20T16:25:33-07:00https://www.saltycrane.com/blog/2017/07/postgres-mac-os-homebrew-notes/<p>
Here are my notes on running postgresql on macOS Mojave installed with Homebrew.
I also wrote some
<a href="/blog/2019/01/how-run-postgresql-docker-mac-local-development/">
notes on running postgres in Docker here</a
>.
</p>
<h4>install postgres</h4>
<pre>
$ brew install postgresql
</pre>
<h4>start postgres</h4>
<pre>
$ brew services start postgresql
</pre>
<h4>restart postgres</h4>
<pre>
$ brew services restart postgresql
</pre>
<h4>postgres version</h4>
<pre>
$ postgres --version
postgres (PostgreSQL) 11.1
</pre>
<h4>check postgres is running (option 1)</h4>
<pre>
$ brew services list
Name Status User Plist
postgresql started eliot /Users/eliot/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
</pre>
<h4>check postgres is running (option 2)</h4>
<pre>
$ ps -ef | grep postgres
502 629 1 0 10Jan19 ?? 0:12.39 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres
502 715 629 0 10Jan19 ?? 0:00.29 postgres: checkpointer
502 716 629 0 10Jan19 ?? 0:02.39 postgres: background writer
502 717 629 0 10Jan19 ?? 0:02.32 postgres: walwriter
502 718 629 0 10Jan19 ?? 0:09.99 postgres: autovacuum launcher
502 719 629 0 10Jan19 ?? 0:45.17 postgres: stats collector
502 720 629 0 10Jan19 ?? 0:00.22 postgres: logical replication launcher
502 73227 19287 0 8:43PM ttys003 0:00.00 grep postgres
</pre>
<h4>postgres log file</h4>
<pre>
/usr/local/var/log/postgres.log
</pre>
<h4>postgres data directory</h4>
<pre>
/usr/local/var/postgres
</pre>
<h4>psql commands</h4>
Start psql:
<pre>
$ psql postgres
</pre>
Help with psql commands:
<pre>
postgres=# \?
</pre>
List databases:
<pre>
postgres=# \l
</pre>
Connect to a database:
<pre>
postgres=# \c mydatabase
</pre>
List tables:
<pre>
mydatabase=# \d
</pre>
Notes on sqlalchemy w/ pyodbc, freetds on Ubuntu
2011-09-28T19:03:19-07:00https://www.saltycrane.com/blog/2011/09/notes-sqlalchemy-w-pyodbc-freetds-ubuntu/<p>These are my notes for connecting to a MS SQL Server using Python on Linux.
My developement environment is Ubuntu 10.10 Maverick (64-bit). The production environment
is Redhat/Centos-based Scientific Linux 6. Here is the layers of stuff:
</p>
<ul>
<li><a href="http://www.freetds.org/">FreeTDS</a>: an open source ODBC driver. Started with version
0.82 and moved to 0.91. Other options:
<a href="http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/28/available-today-preview-release-of-the-sql-server-odbc-driver-for-linux.aspx">
Microsoft ODBC driver for Linux</a> Free, currently only for 64-bit Redhat.
<a href="http://www.easysoft.com/products/data_access/odbc-sql-server-driver/index.html">
Easysoft ODBC driver
</a> Commercial, not free.
</li>
<li><a href="http://www.unixodbc.org/">unixODBC</a>: driver manager, a thin wrapper around the ODBC driver.
Version 2.2 (may move to version 2.3 if we use the Microsoft driver).
</li>
<li><a href="http://code.google.com/p/pyodbc/">pyodbc</a>: python library.
Started with 2.1.11, currently using 3.0.5.
I chose pyodbc over pymssql because, after minimal
research, it seemed to be the preferred choice.
Other options: <a href="http://code.google.com/p/pymssql/">pymssql</a> (GPL, doesn't use ODBC),
<a href="http://www.egenix.com/products/python/mxODBC/">mxODBC</a>
(commercial, much better documentation, may be more robust)
</li>
<li><a href="http://www.sqlalchemy.org/">SQLAlchemy</a>: wraps pyodbc, provides an ORM and better interface.
I chose to use sqlalchemy because it supported named parameters.
pyodbc only supported the 'qmark' paramstyle and pymssql returns
'pyformat' as the paramstyle, but it seems to actually be 'format'.
See <a href="http://www.python.org/dev/peps/pep-0249/">PEP-0249</a>
for more information on paramstyle.
See <a href="http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.text">
http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.text</a>
for more information on using named parameters.
</li>
</ul>
<h4 id="contents">Contents</h4>
<ul>
<li><a href="#install">Install (on Ubuntu)</a></li>
<li><a href="#connect-tsql">Try to connect using tsql (FreeTDS)</a></li>
<li><a href="#connect-python">Try to connect using Python</a></li>
<li><a href="#unicode-error">Error inserting unicode</a></li>
<li><a href="#select-minus-1-bug">Pyodbc SELECT -1 bug</a></li>
<li><a href="#list-tables">How to list tables in the database with sqlalchemy</a></li>
<li><a href="#pyodbc-freetds-truncates-varchar-instead-of-error">
pyodbc/FreeTDS truncates a varchar(max) upon insert instead of raising an error
<em>(Install FreeTDS 0.91 from source)</em>
</a></li>
<li><a href="#segfault">Segfault error when using Free TDS 0.91 and pyodbc 2.1.11</a></li>
<li><a href="#sql-server-equivalent-of-mysqls-limit">What is the MS SQL server equivalent of MySQL's LIMIT in a SELECT</a></li>
<li><a href="#versions">How to get the versions of stuff</a></li>
<li><a href="#microsoft-driver">Install the Microsoft ODBC driver for Linux</a></li>
<li><a href="#install-mxodbc">Install mxodbc</a></li>
<li><a href="#unixodbc-gui-tools">unixODBC GUI tools</a></li>
<li><a href="#sql-developer-ubuntu">Install SQL Developer on Ubuntu</a></li>
<li><a href="#calling-sprocs">Calling sprocs from sqlalchemy and pyodbc</a></li>
<li><a href="#errors">Other misc errors</a></li>
<li><a href="#install-pyodbc-sl6">Install pyodbc on Scientific Linux 6</a></li>
<li><a href="#see-also">See also</a></li>
<li><a href="#location-odbcinst-file">How to find the location of the odbcinst.ini file</a></li>
</ul>
<h4 id="install">Install (on Ubuntu)</h4>
<p>Set up odbc, freetds. Notes thanks to
<a href="http://www.pauldeden.com/2008/12/how-to-setup-pyodbc-to-connect-to-mssql.html">
http://www.pauldeden.com/2008/12/how-to-setup-pyodbc-to-connect-to-mssql.html
</a>
</p>
<pre class="console">$ sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc </pre>
<p>Edit <code>/etc/odbcinst.ini</code>:</p>
<pre>[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
TDS Version = 7.2
client charset = utf-8</pre>
<p><em>UPDATE 2012-07-09:</em> On Ubuntu 12.04 (64-bit), the libraries are in a different location.
Also, Ubuntu 12.04 includes FreeTDS version 0.91.
</p>
<pre>[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
TDS Version = 7.2
client charset = utf-8</pre>
<p>Install pyodbc</p>
<pre class="console">$ pip install pyodbc </pre>
<p>Install sqlalchemy</p>
<pre class="console">$ pip install SQLAlchemy </pre>
<p>This installed the following versions:</p>
<pre class="console">$ apt-cache show tdsodbc | grep Version:
Version: 0.82-7</pre>
<pre class="console">$ odbcinst --version
unixODBC 2.2.14</pre>
<pre class="console">$ pip freeze | grep pyodbc
pyodbc==2.1.11</pre>
<pre class="console">$ pip freeze | grep Alchemy
SQLAlchemy==0.7.3</pre>
<h4 id="connect-tsql">Try to connect using tsql (FreeTDS)</h4>
<p>Run the following command to ensure you can connect using tsql.
For more information see: <a href="http://www.freetds.org/userguide/confirminstall.htm">http://www.freetds.org/userguide/confirminstall.htm</a>
See also: <a href="http://www.freetds.org/userguide/serverthere.htm">FreeTDS User Guide: Chapter 8. Troubleshooting: Is the server there?</a>
</p>
<pre class="console">$ tsql -S 10.2.14.18 -U myusername -P mypassword
locale is "LC_CTYPE=en_US.UTF-8;LC_NUMERIC=en_US.UTF-8;LC_TIME=en_US.UTF-8;LC_COLLATE=C;LC_MONETARY=en_US.UTF-8;LC_MESSAGES=en_US.UTF-8;LC_PAPER=en_US.UTF-8;LC_NAME=en_US.UTF-8;LC_ADDRESS=en_US.UTF-8;LC_TELEPHONE=en_US.UTF-8;LC_MEASUREMENT=en_US.UTF-8;LC_IDENTIFICATION=en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
</pre>
<h4 id="connect-python">Try to connect using Python</h4>
<p>pyodbc - this works</p>
<pre class="python">
import pyodbc
conn = pyodbc.connect(
'DRIVER={FreeTDS};SERVER=dev01.myserver.com;'
'DATABASE=MyDatabase;UID=MyUsername;PWD=mypassword;port=1234;'
'TDS_Version=8.0;')
print conn
</pre>
<p>sqlalchemy - this doesn't work</p>
<pre class="python">
import sqlalchemy
engine = sqlalchemy.create_engine(
'mssql+pyodbc://MyUsername:mypassword@dev01.myserver.com:1234/MyDatabase')
conn = engine.connect()
print conn
</pre>
<pre>
Traceback (most recent call last):
File "debug_sqlalchemy.py", line 22, in <module>
conn = engine.connect()
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 2310, in connect
return self._connection_cls(self, **kwargs)
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 872, in __init__
self.__connection = connection or engine.raw_connection()
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 2396, in raw_connection
return self.pool.unique_connection()
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 169, in unique_connection
return _ConnectionFairy(self).checkout()
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 370, in __init__
rec = self._connection_record = pool._do_get()
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 696, in _do_get
con = self._create_connection()
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 174, in _create_connection
return _ConnectionRecord(self)
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 255, in __init__
self.connection = self.__connect()
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/pool.py", line 315, in __connect
connection = self.__pool._creator()
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/engine/strategies.py", line 80, in connect
return dialect.connect(*cargs, **cparams)
File "/home/saltycrane/.virtualenvs/myproject/lib/python2.6/site-packages/sqlalchemy/engine/default.py", line 275, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnectW)') None None
</pre>
<p>sqlalchemy - this works</p>
<pre class="python">
import sqlalchemy
import urllib
engine = sqlalchemy.create_engine(
'mssql+pyodbc:///?odbc_connect=%s' % (
urllib.quote_plus(
'DRIVER={FreeTDS};SERVER=dev01.myserver.com;'
'DATABASE=MyDatabase;UID=MyUsername;PWD=mypassword;port=1234;'
'TDS_Version=8.0;')))
conn = engine.connect()
print conn
</pre>
<p>sqlalchemy - this works also</p>
<pre class="python">
import pyodbc
import sqlalchemy
def connect():
return pyodbc.connect(
'DRIVER={FreeTDS};SERVER=dev01.myserver.com;'
'DATABASE=MyDatabase;UID=MyUsername;PWD=mypassword;port=1234;'
'TDS_Version=8.0;')
engine = sqlalchemy.create_engine('mssql://', creator=connect)
conn = engine.connect()
print conn
</pre>
<p>You can also connect by specifying the path to the driver directly and bypass /etc/odbcinst.ini.
Not sure of the disadvantages of doing this.<p>
<pre class="python">
SQL_SERVER_HOST = '10.5.18.11'
SQL_SERVER_UID = 'myusername'
SQL_SERVER_PWD = 'mypassword'
FREETDS_DRIVER = '/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so'
before_quoted = ';'.join(
[
'DRIVER={}'.format(FREETDS_DRIVER),
'SERVER={}'.format(SQL_SERVER_HOST),
'DATABASE=mydatabase',
'UID={}'.format(SQL_SERVER_UID),
'PWD={}'.format(SQL_SERVER_PWD),
'PORT=1433',
'TDS_VERSION=7.2'
]
)
print before_quoted + '\n'
connection_string = 'mssql+pyodbc:///?odbc_connect={}'.format(
urllib.quote_plus(before_quoted))
print connection_string + '\n'
engine = sqlalchemy.create_engine(connection_string)
print engine
</pre>
<pre>DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so;SERVER=10.5.18.11;DATABASE=mydatabase;UID=myusername;PWD=mypassword;PORT=1433;TDS_VERSION=7.2
mssql+pyodbc:///?odbc_connect=DRIVER%3D%2Fusr%2Flib%2Fx86_64-linux-gnu%2Fodbc%2Flibtdsodbc.so%3BSERVER%3D10.5.18.11%3BDATABASE%3Dmydatabase%3BUID%3Dmyusername%3BPWD%3Dmypassword%3BPORT%3D1433%3BTDS_VERSION%3D7.2
Engine(mssql+pyodbc:///?odbc_connect=DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so;SERVER=10.5.18.11;DATABASE=mydatabase;UID=myusername;PWD=mypassword;PORT=1433;TDS_VERSION=7.2)
</pre>
<h5>References</h5>
<ul>
<li><a href="http://www.pauldeden.com/2008/12/how-to-setup-pyodbc-to-connect-to-mssql.html">
http://www.pauldeden.com/2008/12/how-to-setup-pyodbc-to-connect-to-mssql.html
</a></li>
<li><a href="http://www.sqlalchemy.org/docs/dialects/mssql.html#id1">
http://www.sqlalchemy.org/docs/dialects/mssql.html#id1
</a></li>
<li><a href="http://kipb7.wordpress.com/2008/06/12/pyodbc-unixodbc-freetds-config/">
http://kipb7.wordpress.com/2008/06/12/pyodbc-unixodbc-freetds-config/
</a></li>
<li><a href="http://stackoverflow.com/questions/4493614/sqlalchemy-equivalent-of-pyodbc-connect-string-using-freetds">
http://stackoverflow.com/questions/4493614/sqlalchemy-equivalent-of-pyodbc-connect-string-using-freetds
</a></li>
</ul>
<h4 id="unicode-error">Error inserting unicode</h4>
<p>If you get this "Error: The incoming tabular data stream (TDS)
remote procedure call (RPC) protocol stream is incorrect." error during insert, it
may be due to unicode in your data.</p>
<pre>Traceback (most recent call last):
File "tmp.py", line 10, in <module>
(unicode(u'some unicode data: \xa1').encode("utf-8"), '123', '123456'),
pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3: The parameter name is invalid. (8005) (SQLExecDirectW)')</pre>
<p>The fix for me was to change the TDS version number. "7.0" and "8.0" did not work. "7.1", "7.2", "9.0", and some others worked as well.
There is
<a href="http://www.freetds.org/userguide/tdshistory.htm">this FreeTDS user guide page on version numbers</a>,
and <a href="http://www.freetds.org/userguide/choosingtdsprotocol.htm">also this other user guide page</a>,
but I'm not sure I'm clear on it. The following works for me:
</p>
<pre class="python">import pyodbc
conn = pyodbc.connect(
'DRIVER={FreeTDS};SERVER=dev01.myserver.com;'
'DATABASE=MyDatabase;UID=MyUsername;PWD=mypassword;port=1234;'
'TDS_Version=7.2;')
cursor = conn.cursor()
cursor.execute(
"""exec MyStoredProc @Param1 = ?, @Param2 = ?, @Param3 = ?""",
(unicode(u'some unicode data: \xa1').encode("utf-8"), '123', '123456'),
)
conn.commit()</pre>
<h5><code>Unrecognized server version info</code> warning</h5>
<p>When I changed <code>TDS_Version</code> to 7.2, I got the following warning:</p>
<pre>/myvirtualenv/lib/python2.6/site-packages/sqlalchemy/connectors/pyodbc.py:119: SAWarning: Unrecognized server version info '95.10.13055'. Version specific behaviors may not function properly. If using ODBC with FreeTDS, ensure server version 7.0 or 8.0, not 4.2, is configured in the FreeTDS configuration.</pre>
<p>This bothered me for a while, but later, when I
<a href="/blog/2011/09/notes-sqlalchemy-w-pyodbc-freetds-ubuntu/#pyodbc-freetds-truncates-varchar-instead-of-error">
upgraded freetds to 0.91</a>, this warning went away.</p>
<h5>See also these pages for more on unicode and pyodbc:</h5>
<ul>
<li><a href="http://stackoverflow.com/questions/947077/using-pyodbc-on-linux-to-insert-unicode-or-utf-8-chars-in-a-nvarchar-mssql-field">
http://stackoverflow.com/questions/947077/using-pyodbc-on-linux-to-insert-unicode-or-utf-8-chars-in-a-nvarchar-mssql-field
</a></li>
<li><a href="http://stackoverflow.com/questions/2192982/django-sql-server-2005-text-encoding-problem">
http://stackoverflow.com/questions/2192982/django-sql-server-2005-text-encoding-problem
</a></li>
</ul>
<h4 id="select-minus-1-bug">Select -1 bug</h4>
<p>Lin alerted me to this bug in pyodbc 2.1.11:
<a href="http://code.google.com/p/pyodbc/issues/detail?id=157">Issue 157: Negative integers are retrieved as positive integers</a>
Executing a <code>SELECT -1</code> returns 2^32-1 (4294967295).
Solution is to apply
<a href="http://code.google.com/p/pyodbc/issues/attachmentText?id=157&aid=1570005000&name=p&token=c5ff21e481c7cdf536d8e138dbab03be">
this patch</a> or upgrade to 3.0.x of pyodbc. I had trouble installing some versions of
pyodbc. This is what worked for me:
<pre class="console">$ pip install --upgrade -e git+https://code.google.com/p/pyodbc#egg=pyodbc </pre>
This gave me:
<pre class="console">$ pip freeze | grep pyodbc
-e git+https://code.google.com/p/pyodbc@404a3ba414ab15863c34db9c49ff0a02caa63600#egg=pyodbc-dev </pre>
<pre>$ python
Python 2.6.6 (r266:84292, Sep 15 2010, 16:22:56)
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'3.0.4-beta01'</pre>
</p>
<h4 id="list-tables">How to list the tables in the database</h4>
<pre class="python">import urllib
import sqlalchemy
# Recommended to be a module-level variable
# http://www.sqlalchemy.org/docs/core/connections.html
engine = sqlalchemy.create_engine(
'mssql+pyodbc:///?odbc_connect=%s' % (
urllib.quote_plus(
'DRIVER={FreeTDS};SERVER=myserver.mydomain.com;'
'DATABASE=MyDatabase;UID=myusername;PWD=mypassword;port=12345;'
'TDS_Version=7.2;')))
metadata = sqlalchemy.MetaData()
metadata.bind = engine
metadata.reflect()
for t in metadata.sorted_tables:
print t.name</pre>
<h4 id="pyodbc-freetds-truncates-varchar-instead-of-error">pyodbc/FreeTDS truncates a varchar(max) upon insert instead of raising an error</h4>
<br><p>When trying to insert a string with more than 100 characters into a varchar(100) field, pyodbc/FreeTDS truncated the string instead of raising an error.
This occurred because I was using FreeTDS version 0.82. I found this out from
<a href="http://comments.gmane.org/gmane.comp.db.tds.freetds/13751">this FreeTDS mailing list thread</a>.
The solution is to upgrade to FreeTDS version 0.91. I could not find any APT packages for Ubuntu 10.10.
Looks like there is a package for Ubuntu Precise (12.04). So I compiled it from source.
This assumes you've installed unixodbc as described above:
<code>$ sudo apt-get install unixodbc unixodbc-dev </code>
</p>
<pre class="console">$ cd /tmp
$ wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
$ tar zxvf freetds-stable.tgz
$ cd freetds-0.91
$ ./configure
$ make
$ sudo make install </pre>
<p>Then edit odbcinst.ini to point at the new library /usr/local/lib/libtdsodbc.so:</p>
<pre>[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
TDS Version = 7.2
client charset = utf-8</pre>
<p>After upgrading, pyodbc now gives me the following error instead of failing silently.</p>
<pre>pyodbc.DataError: ('22001', '[22001] [FreeTDS][SQL Server]String or binary data would be truncated. (8152) (SQLExecDirectW)')</pre>
<p>References:</p>
<ul>
<li><a href="http://www.unixodbc.org/doc/FreeTDS.html">http://www.unixodbc.org/doc/FreeTDS.html
</a></li>
<li><a href="http://serverfault.com/questions/312895/compiling-freetds-0-91-on-ubuntu-11-04-x64">
http://serverfault.com/questions/312895/compiling-freetds-0-91-on-ubuntu-11-04-x64
</a></li>
<li><a href="http://www.freetds.org/userguide/config.htm#EVERYONE">
http://www.freetds.org/userguide/config.htm#EVERYONE
</a></li>
</ul>
<h4 id="segfault">Segfault error when using FreeTDS 0.91 and pyodbc 2.1.11</h4>
<p>Got this error trying to connect to a database on production (Scientific Linux 6)</p>
<pre>*** glibc detected *** python: free(): invalid next size (fast): 0x00000000019ed440 ***</pre>
<p>Solution was to upgrade pyodbc to 3.0.5 because there was a bug.</p>
<ul>
<li><a href="http://code.google.com/p/pyodbc/issues/detail?id=170">
http://code.google.com/p/pyodbc/issues/detail?id=170
</a></li>
<li><a href="http://code.google.com/p/pyodbc/issues/detail?id=212">
http://code.google.com/p/pyodbc/issues/detail?id=212
</a></li>
</ul>
<h4 id="sql-server-equivalent-of-mysqls-limit">What is the MS SQL server equivalent of MySQL's LIMIT in a SELECT</h4>
<p>TOP
See <a href="http://www.w3schools.com/sql/sql_top.asp">http://www.w3schools.com/sql/sql_top.asp</a>
</p>
<pre>SELECT TOP * FROM mytable</pre>
<h4 id="versions">How to get the versions of stuff</h4>
<ul>
<li>unixODBC
<pre>$ odbcinst --version
unixODBC 2.3.0</pre>
or
<pre>$ odbc_config --version
2.3.0</pre>
</li>
<li>freetds
<pre>$ tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v0.91
freetds.conf directory: /etc
MS db-lib source compatibility: yes
Sybase binary compatibility: yes
Thread safety: yes
iconv library: yes
TDS version: 4.2
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: no</pre>
</li>
<li>pyodbc
<pre>$ python
Python 2.7.0+ (r27:82500, Sep 15 2010, 18:14:55)
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'3.0.5'</pre>
</li>
</ul>
<h4 id="microsoft-driver">Trying out the Microsoft ODBC driver for Linux</h4>
<p>This was installed on Scientific Linux 6. Ubuntu is not supported.</p>
<ul>
<li><a href="http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/28/available-today-preview-release-of-the-sql-server-odbc-driver-for-linux.aspx">
http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/28/available-today-preview-release-of-the-sql-server-odbc-driver-for-linux.aspx
</a></li>
<li><a href="http://www.microsoft.com/download/en/details.aspx?id=28160">
http://www.microsoft.com/download/en/details.aspx?id=28160
</a></li>
<li><a href="http://msdn.microsoft.com/en-us/library/hh568455%28v=sql.110%29.aspx">
http://msdn.microsoft.com/en-us/library/hh568455%28v=sql.110%29.aspx
</a></li>
<li><a href="http://www.connectionstrings.com/sql-server-2008">
http://www.connectionstrings.com/sql-server-2008
</a></li>
</ul>
<p>Install make</p>
<pre>$ yum install make </pre>
<p>Followed instructions to install the unixODBC driver manager and the driver.
It installed OK.</p>
<pre class="console">
$ sudo su root
$ tar xvf sqlncli-11.0.1720.0.tar.gz
$ cd sqlncli-11.0.1720.0
$ ./build_dm.sh --help
$ ./build_dm.sh --download-url=file://../unixODBC-2.3.0.tar.gz
$ cd /tmp/unixODBC.8922.6568.26033/unixODBC-2.3.0; make install
$ sudo su root
$ tar xvf sqlncli-11.0.1720.0.tar.gz
$ cd sqlncli-11.0.1720.0
$ ./install.sh --help
$ cp /etc/odbcinst.ini /etc/odbcinst.ini.bak
$ ./install.sh verify
Microsoft SQL Server ODBC Driver V1.0 for Linux Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft SQL Server ODBC Driver V1.0 for Linux
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 or later installed .................... OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .... NOT FOUND
$ ./install.sh install
...
Enter YES to accept the license or anything else to terminate the installation: YES
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 or later installed .................... OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .... NOT FOUND
Microsoft SQL Server ODBC Driver V1.0 for Linux files copied ................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft SQL Server ODBC Driver V1.0 for Linux registered ........... INSTALLED
Install log created at /tmp/sqlncli.31091.8029.6563/install.log.
One or more steps may have an *. See README for more information regarding
these steps.
$ odbcinst -q -d -n "SQL Server Native Client 11.0"
[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0
UsageCount=1
</pre>
<p>Connection string: Driver is "SQL Server Native Client 11.0".
Port is specified with a comma after the host. Thanks to
<a href="http://www.connectionstrings.com/sql-server-2008">
http://www.connectionstrings.com/sql-server-2008
</a></p>
<pre> connection = pyodbc.connect(
';'.join([
'DRIVER={SQL Server Native Client 11.0}',
'SERVER=%s,%s' % (db_profile['host'], db_profile['port']),
'DATABASE=%s' % db_profile['name'],
'UID=%s' % db_profile['user'],
'PWD=%s' % db_profile['password'],
]) + ';')</pre>
<p>Ran my app and got the following error message:</p>
<pre>Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0' : file not found (0) (SQLDriverConnect)")</pre>
<p>That's a dumb error message.
<code>/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0</code> existed and it had the correct
permissions. Turns out it was missing dependencies. Google help:
<a href="http://mailman.unixodbc.org/pipermail/unixodbc-support/2011-July/002902.html">
http://mailman.unixodbc.org/pipermail/unixodbc-support/2011-July/002902.html
</a> and
<a href="http://www.linuxquestions.org/questions/fedora-35/how-to-install-libcrypto-so-4-a-594511/">
http://www.linuxquestions.org/questions/fedora-35/how-to-install-libcrypto-so-4-a-594511/
</a>.
</p>
<pre>$ ldd /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0
linux-vdso.so.1 => (0x00007fff5258d000)
libcrypto.so.6 => not found
libdl.so.2 => /lib64/libdl.so.2 (0x00007feec91fd000)
libodbc.so.1 => /usr/lib64/libodbc.so.1 (0x00007feec8f90000)
librt.so.1 => /lib64/librt.so.1 (0x00007feec8d88000)
libssl.so.6 => not found
libuuid.so.1 => /lib64/libuuid.so.1 (0x00007feec8b83000)
libodbcinst.so.1 => /usr/lib64/libodbcinst.so.1 (0x00007feec896c000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007feec868c000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007feec844b000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007feec8144000)
libm.so.6 => /lib64/libm.so.6 (0x00007feec7ebf000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007feec7ca9000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007feec7a8d000)
libc.so.6 => /lib64/libc.so.6 (0x00007feec76eb000)
/lib64/ld-linux-x86-64.so.2 (0x00007feec97b0000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007feec74bf000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007feec72bc000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007feec70b1000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007feec6eaf000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007feec6c95000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x00007feec6a75000)</pre>
<p>libcrypto.so.6 and libssl.so.6 are missing.</p>
<pre>$ yum install openssl098e
$ ldd /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0
linux-vdso.so.1 => (0x00007fff5c7fe000)
libcrypto.so.6 => /usr/lib64/libcrypto.so.6 (0x00007f7ebcdd8000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f7ebcbd4000)
libodbc.so.1 => /usr/lib64/libodbc.so.1 (0x00007f7ebc966000)
librt.so.1 => /lib64/librt.so.1 (0x00007f7ebc75e000)
libssl.so.6 => /usr/lib64/libssl.so.6 (0x00007f7ebc50f000)
libuuid.so.1 => /lib64/libuuid.so.1 (0x00007f7ebc30a000)
libodbcinst.so.1 => /usr/lib64/libodbcinst.so.1 (0x00007f7ebc0f3000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f7ebbe14000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f7ebbbd1000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007f7ebb8ca000)
libm.so.6 => /lib64/libm.so.6 (0x00007f7ebb646000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7ebb42f000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f7ebb213000)
libc.so.6 => /lib64/libc.so.6 (0x00007f7ebae72000)
libz.so.1 => /lib64/libz.so.1 (0x00007f7ebac5c000)
/lib64/ld-linux-x86-64.so.2 (0x00007f7ebd4e3000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f7ebaa59000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f7eba82d000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f7eba621000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f7eba41f000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f7eba205000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f7eb9fe5000)</pre>
<p>Good. It works.</p>
<h4 id="install-mxodbc">Install mxodbc</h4>
<pre class="console">$ python -c 'print("UCS%s"%len(u"x".encode("unicode-internal")))'
UCS4
$ pip install http://downloads.egenix.com/python/egenix-mx-base-3.2.3.linux-x86_64-py2.7_ucs4.prebuilt.zip
$ pip install http://downloads.egenix.com/python/egenix-mxodbc-3.1.1.linux-x86_64-py2.7_ucs4.prebuilt.zip
$ #copy license files to "site-packages" directory
</pre>
<h4 id="unixodbc-gui-tools">unixODBC GUI tools</h4>
<p>I didn't end up actually using this. I think this is useful if you need to set up DSNs (e.g. if you're using mxodbc).</p>
<pre class="console">$ sudo apt-get install unixodbc-bin </pre>
<p>Description: Graphical tools for ODBC management and browsing
This package contains three graphical applications for use with
unixODBC, the Open DataBase Connectivity suite: ODBCConfig, a graphical
configuration tool for managing database drivers and access to
individual databases; DataManager, a simple browser and query tool for
ODBC databases; and odbctest, a tool for testing the ODBC API itself.
</p>
<p>Provides:</p>
<pre>/usr/bin/DataManager
/usr/bin/DataManagerII
/usr/bin/ODBCConfig
/usr/bin/odbctest</pre>
<h4 id="sql-developer-ubuntu">Installing SQL Developer on Ubuntu</h4>
<p>For a GUI interface to MS SQL Server, I like Oracle's SQL Developer better than Eclipse. Thanks Kris. (Too bad it doesn't execute stored procedures.)</p>
<pre>
- $ sudo apt-get install openjdk-6-jdk
- Create an Oracle account
- Download "Oracle SQL Developer for other platforms" from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
- $ unzip sqldeveloper-3.1.07.42-no-jre.zip
- $ cd sqldeveloper
- $ sh sqldeveloper.sh
Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path will be stored in ~/.sqldeveloper/jdk
usr/lib/jvm/java-6-openjdk
http://djiang.wordpress.com/2007/02/27/using-oracle-sqldeveloper-to-access-sqlserver/
Download jTDS from here: http://sourceforge.net/projects/jtds/
mkdir jtds-1.2.5
mv jtds-1.2.5-dist.zip jtds-1.2.5
cd jtds-1.2.5
unzip jtds-1.2.5-dist.zip
- Start Oracle’s SQLDeveloper, Tools->Preferences->Database->Third Party JDBC Drivers. Click “Add Entry” and point to the jtds-1.2.jar
- File -> New... -> Database Connection -> choose SQLServer tab, type in hostname, port, username and password. It appears that the initial connection name has to be the same as the database and you can click the “Retrieve database” button. Once you found the database, you can rename the connection.
</pre>
<h4 id="calling-sprocs">Calling stored procs from sqlalchemy and pyodbc</h4>
<pre class="python">
connection_string = ';'.join([
'DRIVER={FreeTDS}',
'SERVER=myhost.com',
'DATABASE=my_database',
'UID=myusername',
'PWD=mypassword',
'port=1433',
'TDS_Version=7.2',
]) + ';'
print connection_string
def use_pyodbc():
import pyodbc
connection = pyodbc.connect(connection_string)
print connection
sql = " ".join([
"EXEC [dbo].my_sproc",
"@my_param = ?",
])
print sql
cursor = connection.cursor()
result = cursor.execute(sql, ('my_value',))
for r in result.fetchall():
print r
print r.a_param
connection.commit()
def use_sqlalchemy():
import urllib
import sqlalchemy
engine = sqlalchemy.create_engine(
'mssql+pyodbc:///?odbc_connect=%s' % (
urllib.quote_plus(connection_string)))
print engine
sql = sqlalchemy.sql.text(" ".join([
"EXEC [dbo].my_sproc",
"@my_param = :my_param",
]))
print sql
result = engine.execute(sql, {'my_param': 'my_value'})
for r in result.fetchall():
for k in r.keys():
try:
print '%50s %s' % (k, getattr(r, k))
except sqlalchemy.exc.InvalidRequestError as e:
print e
break
</pre>
<h4 id="install-pyodbc-sl6">Install pyodbc on Scientific Linux 6</h4>
<p>you will also need freetds installed</p>
<pre class="console">$ yum install unixODBC-devel
$ yum install gcc-c++
$ pip install pyodbc </pre>
<h4 id="errors">Other misc errors</h4>
<ul>
<li>
<pre>ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL Server]Statement(s) could not be prepared. (8180) (SQLExecDirectW)') </pre>
Problem was missing commas.
</li>
<li>
<pre>DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)') None None</pre>
<ul>
<li>This occurred when I could not connect to the database server. To troubleshoot this, see:
<a href="http://www.freetds.org/userguide/serverthere.htm">FreeTDS User Guide: Chapter 8. Troubleshooting: Is the server there?</a>
</li>
<li>This also occurred when I did not configure <code>/etc/odbcinst.ini</code> correctly
as described in the <a href="#install">Install section</a>
</li>
</ul>
</li>
</ul>
<h4 id="see-also">See also</h4>
<ul>
<li><a href="http://www.easysoft.com/developer/interfaces/odbc/linux.html">
Easysoft Linux ODBC documentation
</a></li>
</ul>
<h4 id="location-odbcinst-file">How to find the location of the odbcinst.ini file</h4>
<pre class="console">$ odbcinst -j
unixODBC 2.3.0
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/eliot/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8</pre>
<h4 id="trace-odbc">Trace odbc</h4>
<p><a href="http://www.easysoft.com/support/kb/kb00945.html">http://www.easysoft.com/support/kb/kb00945.html</a>
Add the following to the odbcinst.ini file. Be sure to turn it off after debugging.</p>
<pre>[ODBC]
Trace = yes
TraceFile = /tmp/my_odbc_tracfile.log</pre>
psycopg2 "could not connect to server" error on Ubuntu
2010-06-08T21:48:24-07:00https://www.saltycrane.com/blog/2010/06/psycopg2-could-not-connect-server-error-ubuntu/<p>While trying to run <code>python manage.py syncdb</code> with
Django 1.2.1, psycopg2 2.2.1 and PostgreSQL 8.4 on Ubuntu Lucid,
I got the following error saying I couldn't connect to the database.
</p>
<pre class="console">$ python manage.py syncdb
Traceback (most recent call last):
File "manage.py", line 11, in <module>
execute_manager(settings)
File "/home/eliot/lib/python-environments/saltycrane121/lib/python2.6/site-packages/django/core/management/__init__.py", line 438, in execute_manager
utility.execute()
File "/home/eliot/lib/python-environments/saltycrane121/lib/python2.6/site-packages/django/core/management/__init__.py", line 379, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/home/eliot/lib/python-environments/saltycrane121/lib/python2.6/site-packages/django/core/management/base.py", line 191, in run_from_argv
self.execute(*args, **options.__dict__)
File "/home/eliot/lib/python-environments/saltycrane121/lib/python2.6/site-packages/django/core/management/base.py", line 218, in execute
output = self.handle(*args, **options)
File "/home/eliot/lib/python-environments/saltycrane121/lib/python2.6/site-packages/django/core/management/base.py", line 347, in handle
return self.handle_noargs(**options)
File "/home/eliot/lib/python-environments/saltycrane121/lib/python2.6/site-packages/django/core/management/commands/syncdb.py", line 52, in handle_noargs
cursor = connection.cursor()
File "/home/eliot/lib/python-environments/saltycrane121/lib/python2.6/site-packages/django/db/backends/__init__.py", line 75, in cursor
cursor = self._cursor()
File "/home/eliot/lib/python-environments/saltycrane121/lib/python2.6/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 136, in _cursor
self.connection = Database.connect(**conn_params)
psycopg2.OperationalError: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
</pre>
<p>I tried connecting via <code>psql</code> and that worked. I looked in
<code>/var/run/postgresql/</code> and found:
</p>
<pre class="console">$ ls -lA /var/run/postgresql
total 8
srwxrwxrwx 1 postgres postgres 0 2010 06/08 21:29 .s.PGSQL.5433
-rw------- 1 postgres postgres 34 2010 06/08 21:29 .s.PGSQL.5433.lock
-rw------- 1 postgres postgres 5 2010 06/08 21:29 8.4-main.pid</pre>
<p>So it turns out my Postgres server is running on port 5433, but
Django (psycopg2) expects to find it at 5432 by default.
So I changed the port number
to 5433 in my <code>settings.py</code> and it worked.
</p>
<p>Note: I looks like the reason my Postgres server was running on port
5433 instead of 5432 is because I previously had PostgreSQL 8.3 installed
on my machine and there were configuration files left in
<code>/etc/postgresql/8.3/</code>
The port setting is configured in <code>/etc/postgresql/8.x/main/postgresql.conf</code>.
</p>
Notes on migrating this blog from SQLite to PostgreSQL using Django
2009-08-07T23:54:14-07:00https://www.saltycrane.com/blog/2009/08/notes-migrating-blog-sqlite-postgresql/<p>Here are my notes on migrating this blog from SQLite to PostgreSQL. For the parts
of my database that were in proper order, the migration was made very easy using
Django's
<a href="http://docs.djangoproject.com/en/dev/ref/django-admin/#dumpdata">./manage.py dumpdata</a>
and
<a href="http://docs.djangoproject.com/en/dev/ref/django-admin/#loaddata-fixture-fixture">
./manage.py loaddata</a> commands. However, the database tables used for storing
the comments on this blog were kind of screwed up because I had
<a href="/blog/2008/07/django-blog-project-9-migrating-blogger/">
previously migrated</a> them from my old Blogger blog. So I had to write
another (not so pretty) script for that.
</p>
<p>Thanks to
<a href="http://stevelord.tumblr.com/post/86674737/migrating-an-sqlite-based-django-app-to-postgresql">
this article</a> for showing me how to use Django's dumpdata and loaddata for
this migration.
</p>
<h4>Create a new Postgres database</h4>
<p>See <a href="/blog/2008/12/card-store-project-2-installing-satchmo-django-postgresql-and-apache-ubuntu-slicehost/#postgres">
my previous notes</a> for creating a Postgres database. I named the new
database "saltycrane_db" owned by "django_user" with password "my_password".
</p>
<h4>Clone and modify Django project</h4>
<ul>
<li>Clone my SaltyCrane project
<pre>hg clone SaltyCrane SaltyCraneNew</pre>
or
<pre>cp -rp SaltyCrane SaltyCraneNew</pre>
</li>
<li>Edit settings.py:
<pre>DATABASE_ENGINE = 'postgresql_psycopg2'
DATABASE_NAME = 'saltycrane_db'
DATABASE_USER = 'django_user'
DATABASE_PASSWORD = my_password'</pre>
</li>
<li>Create database tables
<pre>python manage.py syncdb</pre>
</li>
</ul>
<h4>Migrate data</h4>
<ul>
<li>Create JSON dumps from the existing SQLite database for my 3 Django apps (myblogapp, comments, and tagging):
<pre>cd /srv/SaltyCrane/iwiwdsmi
./manage.py dumpdata myblogapp > dump_myblogapp.json
./manage.py dumpdata comments > dump_comments.json
./manage.py dumpdata tagging > dump_tagging.json</pre>
</li>
<li>Load the JSON data dumps to the new Postgres database:
<pre>cd /srv/SaltyCraneNew/iwiwdsmi
./manage.py loaddata ../../SaltyCrane/iwiwdsmi/dump_tagging.json
./manage.py loaddata ../../SaltyCrane/iwiwdsmi/dump_myblogapp.json</pre>
</li>
</ul>
<p>However, loading comments didn't work because I had some missing fields so...</p>
<h4>Migration script</h4>
<p>...I wrote a migration script:</p>
<pre class="python">import setup_environment
import simplejson
from pprint import pprint
from django.contrib.comments.models import Comment
from django.contrib.contenttypes.models import ContentType
from iwiwdsmi.myblogapp.models import Post
JSON_FILENAME = "/srv/SaltyCrane/iwiwdsmi/dump_comments.json"
ct = ContentType.objects.get(name='post', app_label='myblogapp')
N_OLD_COMMENTS = 4000
def main():
delete_all_comments()
create_dummy_comments()
pydata = open_json_file(JSON_FILENAME)
save_items_to_database(pydata)
delete_dummy_comments()
def delete_all_comments():
""" Clears the database of all comments.
"""
comments = Comment.objects.all()
comments.delete()
print "All comments deleted."
def create_dummy_comments():
""" Create a bunch of filler dummy comments
"""
for i in range(N_OLD_COMMENTS):
c = Comment()
c.comment = "Filler comment."
c.content_type = ct
c.ip_address = None
c.is_public = False
c.is_removed = False
c.object_pk = 243
c.site_id = 1
c.user_email = "filler@email.com"
c.user_name = "Filler"
c.save()
print "Filler comments created."
def delete_dummy_comments():
comments = Comment.objects.filter(is_public=False)
comments.delete()
print "Dummy comments deleted."
def open_json_file(filename):
""" open the json file and return the python data structure
"""
json_fp = open(filename)
pydata = simplejson.load(json_fp)
return pydata
def save_items_to_database(pydata):
""" Process the python data structure and save to the new database
"""
for item in pydata:
pprint(item)
pk = item['pk']
item = item['fields']
if item['is_public'] and not item['is_removed']:
c = Comment.objects.get(id=pk)
c.comment = item['comment']
c.content_type = ct
c.ip_address = get_ip_address(item['ip_address'])
c.is_public = item['is_public']
c.is_removed = item['is_removed']
c.object_pk = item['object_pk']
c.site_id = 1
c.submit_date = item['submit_date']
c.user_id = item['user']
c.user_email = item['user_email']
c.user_name = item['user_name']
c.user_url = item['user_url']
c.save()
def get_ip_address(ip):
""" Handle bad input for IP addresses
"""
if ip == "" or ip == "unknown":
return None
else:
return ip
if __name__ == '__main__':
main()</pre>
Postgres notes
2009-05-10T00:22:49-07:00https://www.saltycrane.com/blog/2009/05/notetoself10/<p>Here are some Postgres notes to myself.</p>
<h4>How to add a column to an existing table </h4>
<p>I'm going to add the "tags" column to the "product" table
</p>
<pre>cd /srv/HandsOnCards/handsoncards
python manage.py sqlall product</pre>
It returns (among other things):
<pre>CREATE TABLE "product_product" (
"id" serial NOT NULL PRIMARY KEY,
"site_id" integer NOT NULL REFERENCES "django_site" ("id") DEFERRABLE INITIALLY DEFERRED,
"name" varchar(255) NOT NULL,
"slug" varchar(80) NOT NULL,
"sku" varchar(255) NULL,
"short_description" text NOT NULL,
"description" text NOT NULL,
"items_in_stock" integer NOT NULL,
"meta" text NULL,
"date_added" date NULL,
"active" boolean NOT NULL,
"featured" boolean NOT NULL,
"ordering" integer NOT NULL,
"weight" numeric(8, 2) NULL,
"weight_units" varchar(3) NULL,
"length" numeric(6, 2) NULL,
"length_units" varchar(3) NULL,
"width" numeric(6, 2) NULL,
"width_units" varchar(3) NULL,
"height" numeric(6, 2) NULL,
"height_units" varchar(3) NULL,
"total_sold" integer NOT NULL,
"taxable" boolean NOT NULL,
"taxClass_id" integer NULL REFERENCES "tax_taxclass" ("id") DEFERRABLE INITIALLY DEFERRED,
"shipclass" varchar(10) NOT NULL,
"tags" varchar(255) NULL,
UNIQUE ("site_id", "sku"),
UNIQUE ("site_id", "slug")
)</pre>
<pre>su postgres
psql handsoncards_db</pre>
<pre>ALTER TABLE product_product ADD tags varchar(255) NULL;
\q</pre>
<pre>exit</pre>
<p>See the <a href="http://www.postgresql.org/docs/8.3/static/sql-altertable.html">
Postgresql ALTER TABLE documentation</a> for more information.
</p>
<h4>Create a new database</h4>
<ul>
<li>
Create a database named <code>django_db</code>.
Assume the user, <code>django_user</code>, has already been created.
<pre>su postgres
psql template1</pre>
<pre>CREATE DATABASE django_db OWNER django_user ENCODING 'UTF8';
\q</pre>
<pre>exit</pre>
</li>
<li>
Configure access to the database. Add the following line to
<code>/etc/postgresql/8.3/main/pg_hba.conf</code>:
<pre>local django_db django_user md5</pre>
</li>
<li>
Restart the postgres server:
<pre>sudo /etc/init.d/postgresql-8.3 restart</pre>
</li>
</ul>
<h4>Give a user the privilege to create databases</h4>
<ul>
<li><pre>su postgres</pre></li>
<li><pre>psql template1</pre></li>
<li>List users
<pre>\du</pre>
</li>
<li><pre>ALTER USER my_username WITH CREATEDB;</pre></li>
<li><pre>\q</pre></li>
<li><pre>exit</pre></li>
</ul>
<p>See <a href="http://www.postgresql.org/docs/8.4/static/sql-alteruser.html">
http://www.postgresql.org/docs/8.4/static/sql-alteruser.html</a></p>
Postgres backup with cron
2008-12-27T12:36:36-08:00https://www.saltycrane.com/blog/2008/12/postgres-backup-cron/<p>So I accidentally deleted my Postgres database on my public server instead
of my local server last night. Luckily, I was still in the testing phase.
I had been meaning to figure out how to backup Postgres but had been putting
it off. Now I've been provided the proper motivation.</p>
<p>The PostgreSQL documentation has a whole
<a href="http://www.postgresql.org/docs/8.3/interactive/backup.html">
chapter devoted to backup</a>. There it describes three methods of
backup: SQL dump, file system level backup, and continuous archiving.
I chose SQL dump.</p>
<h4>Manual SQL dump</h4>
<p>To do a manual dump, here is what I did. (As you might guess,
handsoncards_db is my database name.)</p>
<pre>su postgres
pg_dump handsoncards_db > /tmp/testdump
exit</pre>
<h4>Schedule backups with cron</h4>
<p>To perform backups at regular intervals I used cron.
Logged in as root, I created a file
<code>handsoncards_db_backup</code> in <code>/etc/cron.d</code>.
(Note, I'm running Ubuntu Intrepid. Cron will automatically start
running this new job without a restart.)
<pre>
# m h dom mon dow user command
45 3 * * * postgres pg_dump handsoncards_db > /srv/backups/handsoncards_db_backup
</pre>
<p>This will create a backup at 3:45am every day.
Be sure to put a newline at the end of the file.</p>
<p>Finally I created the backup directory and made postgres the owner.</p>
<pre>mkdir -p /srv/backups
chown postgres:postgres /srv/backups</pre>
<h4>Restore a database from the dump file</h4>
<p>If necessary, delete the old database. Then create a new databse and restore
from the dump file.</p>
<pre>su postgres
psql template1</pre>
<pre>CREATE DATABASE handsoncards_db OWNER django_user ENCODING 'UTF8';
\q</pre>
<pre>psql handsoncards_db < /srv/backups/handsoncards_db_backup
exit</pre>
<p>NOTE: if you get a permission denied error when trying to restore, check the
Unix permissions on the backup file and all the parent directories.</p>
Card store project #2: Installing Satchmo, Django, PostgreSQL, and Apache on Ubuntu at Slicehost
2008-12-11T21:21:22-08:00https://www.saltycrane.com/blog/2008/12/card-store-project-2-installing-satchmo-django-postgresql-and-apache-ubuntu-slicehost/<p>As I mentioned in my
<a href="http://www.saltycrane.com/blog/2008/12/card-store-project-1-plans/">previous
post</a>, I'm planning to set up an e-commerce site using the
<a href="http://www.satchmoproject.com/">Satchmo</a> shopping cart framework.
Satchmo is built on the
<a href="http://www.satchmoproject.com/">Django</a> web framework which is
written in the
<a href="http://www.python.org/">Python</a> programming language. Satchmo
has a lot of <a href="http://www.satchmoproject.com/docs/svn/features.html">features</a>
built in which means it saves you a lot of work implementing them yourself.
Check out <a href="http://www.youtube.com/watch?v=d42a4g650Ws">this video
introduction to Satchmo</a> at this year's DjangoCon for more information.</p>
<p>After reading <a href="http://groups.google.com/group/satchmo-users/browse_thread/thread/b453d6453b6a28cf?pli=1">
this discussion</a> on the Satchmo mailing list, I decided to use
<a href="http://www.slicehost.com/">Slicehost</a> for hosting my site.
Their cheapest plan provides 256MB of RAM for $20/month.
Here are my notes on setting up Satchmo and Django with
<a href="http://www.postgresql.org/">PostgreSQL</a>,
<a href="http://httpd.apache.org/">Apache</a>,
and <a href="http://www.modpython.org/">mod_python</a> on
<a href="http://www.ubuntu.com/">Ubuntu</a> Intrepid at Slicehost.
It seems <a href="http://www.lighttpd.net/">lighttpd</a> and
<a href="http://nginx.net/">nginx</a> are popular lightweight alternatives to
Apache on VPS setups. I do not know too much about this, but may explore these
in the future.
</p>
<p>Note, I don't describe how to setup a Satchmo project below. Maybe I will
write another post about that later... For more information, see the
<a href="http://www.satchmoproject.com/docs/rel/0.8.1/new_installation.html">
Satchmo Installation documentation</a>. <em>Update 2008-12-12: I did write
another blog post-- see <a href="http://www.saltycrane.com/blog/2008/12/card-store-project-3-installing-satchmo-part-2/">
Installing Satchmo, part 2</a>.</em>
</p>
<h4>Setup Slicehost</h4>
<ul>
<li>Sign up for an account at <a href="http://www.slicehost.com/">http://www.slicehost.com/</a>
I chose Ubuntu Intrepid for my operating system and named my slice "toad".
</li>
<li>I followed the following two excellent Slicehost tutorials:
<a href="http://articles.slicehost.com/2008/4/25/ubuntu-hardy-setup-page-1">Ubuntu Setup Part 1</a>
and <a href="http://articles.slicehost.com/2008/4/25/ubuntu-hardy-setup-page-2">Part 2</a>.
</li>
<li>An important step is setting the system locale. Since I'm in the United States,
I used the following commands (run as root):
<pre>locale-gen en_US.UTF-8
update-locale LANG=en_US.UTF-8</pre>
</li>
</ul>
<h4>Install apache, mod_python, postgresql, and postgres python bindings</h4>
<ul>
<li>Run as root (or use sudo):
<pre>apt-get update
apt-get upgrade
apt-get dist-upgrade
apt-get install apache2 apache2-mpm-prefork
apt-get install libapache2-mod-python
apt-get install postgresql
apt-get install python-psycopg2</pre>
</li>
</ul>
<h4>Configure apache</h4>
<ul>
<li>I used the following Slicehost tutorials:
<a href="http://articles.slicehost.com/2008/4/25/ubuntu-hardy-installing-apache-and-php5">Install Apache</a>,
<a href="http://articles.slicehost.com/2008/4/28/ubuntu-hardy-apache-configuration-1">Configure Apache Part 1</a>,
<a href="http://articles.slicehost.com/2008/4/28/ubuntu-hardy-apache-configuration-2">Configure Apache Part 2</a>
</li>
<li>Of particular note, when installing apache, I got this warning:
<pre>apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.0.1 for ServerName</pre>
Fix it by setting the ServerName in <code>/etc/apache2/apache2.conf</code>:
<pre>ServerName yourdomain.com
ServerTokens Prod</pre>
See the <a href="http://httpd.apache.org/docs/2.2/mod/core.html#servername">Apache
docs</a> for more information on ServerName.
</li>
</ul>
<h4>Install Django 1.0.2</h4>
<ul>
<li>Run the following as root:
<pre>cd /srv
wget http://www.djangoproject.com/download/1.0.2/tarball/
tar zxvf Django-1.0.2-final.tar.gz
mkdir python-packages
cd python-packages
ln -s ../Django-1.0.2-final/django</pre>
</li>
</ul>
<h4>Test Django (optional)</h4>
<ul>
<li>Run the following as root:
<pre>export PYTHONPATH=/srv/python-packages
/srv/Django-1.0.2-final/django/bin/django-admin.py startproject testdjango</pre>
</li>
<li>Edit <code>/etc/apache2/httpd.conf</code>:
<pre><location "/">
SetHandler python-program
PythonHandler django.core.handlers.modpython
SetEnv DJANGO_SETTINGS_MODULE testdjango.settings
PythonPath "['/srv', '/srv/python-packages'] + sys.path"
PythonDebug On
</location></pre>
</li>
<li>Restart apache:
<pre>/etc/init.d/apache2 restart</pre>
Navigate to your slice's IP address in your browser and you
should see the Django "It worked!" page.
</li>
</ul>
<h4>Install Satchmo</h4>
<p>Satchmo requires a number of external Python packages besides
Django. Unfortunately, there isn't one standard way of installing
Python packages. I decided to install most of them using Ubuntu's
APT package management and one of them using
<a href="http://peak.telecommunity.com/DevCenter/EasyInstall">easy_install</a>,
and one of them from source.
Alternatively, I could have installed most of the packages with
easy_install, and one of them with APT (and one of them from source).
Or, I could have done it another way... sigh.
</p>
<p>Note, I use the directory <code>/srv/python-packages</code> to
store symbolic links to my needed python packages. Then I add this
directory to my Python Path in the PYTHONPATH environment variable or in
the Apache <code>httpd.conf</code> file. See my post,
<a href="http://www.saltycrane.com/blog/2008/08/somewhere-your-python-path/">
Somewhere on your Python path</a> for more information.
</p>
<ul>
<li>Install Satchmo prerequisites. Run the following commands as root:
<br><b>Install APT packages:</b>
<pre>apt-get update
apt-get upgrade
apt-get install python-crypto
apt-get install python-yaml
apt-get install python-imaging
apt-get install python-reportlab
apt-get install python-trml2pdf</pre>
<br><b>Install django-registration using easy_install:</b>
<pre>apt-get install python-setuptools python-dev build-essential
easy_install django-registration</pre>
<br><b>Install comment_utils source:</b>
<pre>apt-get install subversion
mkdir -p /srv/python-packages/dist
cd /srv/python-packages/dist
svn co http://django-comment-utils.googlecode.com/svn/trunk/comment_utils/
mv comment_utils comment_utils_rev92
cd /srv/python-packages
ln -s dist/comment_utils_rev92 comment_utils</pre>
</li>
<li>Install Satchmo 0.8. Run the following as root: <em>Update 2008-12-12:
I changed this to use release 0.8 instead of the SVN trunk version.</em>
<pre>cd /srv
wget http://www.satchmoproject.com/snapshots/satchmo-0.8.tgz
tar zxvf satchmo-0.8.tgz
cd /srv/python-packages
ln -s ../satchmo-0.8/satchmo</pre>
To install the SVN trunk version instead,
<pre>cd /srv
svn co svn://satchmoproject.com/satchmo/trunk
mv trunk satchmo_revXXXX
cd /srv/python-packages
ln -s ../satchmo_revXXXX/satchmo</pre>
</li>
</ul>
<h4 id="postgres">Setup PostgreSQL</h4>
<p>I followed the
<a href="http://www.punteney.com/writes/setting-django-slicehost-ubuntu-hardy-postgres-apa/">instructions
at Punteney.com</a> for setting up Django and Postgres on Slicehost.
</p>
<ul>
<li>If you didn't already install Postgres above, run the following as root:
<pre>apt-get install postgresql
apt-get install python-psycopg2</pre>
</li>
<li>Change the password for the "postgres" Unix user. (run as root or use sudo)
<pre>passwd -d postgres
su postgres -c passwd</pre>
</li>
<li>Change password for "postgres" user in the database.
(From Punteney's article, "It's convenient
to have the two passwords match, but not required.")
<pre>su postgres -c psql template1</pre>
<pre>ALTER USER postgres WITH PASSWORD 'postgres_user_password';
template1=\q</pre>
</li>
<li>Create django user:
<pre>su postgres
createuser -P django_user</pre>
Punteney says to answer no to all here.
Remember the password to put in Django settings.py file. (For this
example, I set the password to <code>my_password</code>.)
</li>
<li>Create a database. Still <code>su</code>'ed as the "postgres" user:
<pre>psql template1</pre>
<pre>CREATE DATABASE django_db OWNER django_user ENCODING 'UTF8';
\q</pre>
<pre>exit</pre>
(exit from postgres su)
</li>
<li>Configure access to the database. Edit
<code>/etc/postgresql/8.3/main/pg_hba.conf</code>:
<pre>local all postgres ident sameuser
local django_db django_user md5</pre>
</li>
<li>Restart the postgres server:
<pre>/etc/init.d/postgresql-8.3 restart</pre>
</li>
<li>In your Django project, use the following database settings in
<code>settings.py</code>:
<pre>DATABASE_ENGINE = 'postgresql_psycopg2'
DATABASE_NAME = 'django_db'
DATABASE_USER = 'django_user'
DATABASE_PASSWORD = 'my_password'
DATABASE_HOST = ''
DATABASE_PORT = ''</pre>
</li>
<li>Run <code>syncdb</code>:
<pre>cd /srv/yourproject
python manage.py syncdb</pre>
</li>
</ul>
<h4>Setup a domain name</h4>
<ul>
<li>Register for a domain name. Google for "domain registration" for
options.
</li>
<li>Follow the
<a href="http://articles.slicehost.com/2007/10/24/creating-dns-records">
Slicehost directions</a> for creating DNS records. This includes
setting the nameservers at your domain registration service to use
Slicehost's nameservers (ns1.slicehost.net, ns2.slicehost.net, ns3.slicehost.net).
</li>
</ul>
MySql notes
2008-11-06T15:33:56-08:00https://www.saltycrane.com/blog/2008/11/mysql-notes/<h4>Using the shell</h4>
<pre>mysql -u root -p -h mymysql.server.hostname.com mydatabase</pre>
<h4>How to show a list of table names</h4>
<pre>SHOW TABLES;</pre>
<h4>How to show the CREATE TABLE statement</h4>
<pre>SHOW CREATE TABLE my_table_name;</pre>
<h4>How to create a database</h4>
<pre>CREATE DATABASE my_database;</pre>
<h4>How to create a user</h4>
<pre>CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'localhost';</pre>
<h4>How to delete a database</h4>
<pre>DROP DATABASE my_database_name;</pre>
<h4>How to delete a table</h4>
<pre>DROP TABLE my_table_name;</pre>
<h4>How to convert a table to unicode</h4>
<pre>ALTER TABLE my_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;</pre>
<h4>How to create an index</h4>
<pre>CREATE INDEX my_index_name ON my_table_name(my_column_name);</pre>
<h4>How to add a column</h4>
<pre>ALTER TABLE my_table_name ADD my_column_name varchar(255) null;</pre>
<h4>How to modify an existing column</h4>
<pre>ALTER TABLE my_table_name MODIFY my_column_name varchar(255) null;</pre>
<ul>
<li><a href="http://stackoverflow.com/questions/212939/how-do-i-modify-a-mysql-column-to-allow-null">
Stackoverflow question/answer</a></li>
<li><a href="http://dev.mysql.com/doc/refman/5.0/en/alter-table.html">
MySql ALTER TABLE syntax</a></li>
</ul>
<h4>How to rename a table</h4>
<pre>ALTER TABLE my_table_name RENAME my_new_table_name;</pre>
<a href="http://dev.mysql.com/doc/refman/5.0/en/alter-table.html">
MySql ALTER TABLE syntax</a><br>
<h4>How to rename a column</h4>
<pre>ALTER TABLE my_table_name CHANGE my_column_name my_new_column_name varchar(255) null;</pre>
<h4>How to count the number of rows in a table</h4>
<pre>SELECT count(*) FROM my_table_name;</pre>
<h4>How to list users</h4>
<pre>SELECT Host, User, Password FROM mysql.user;</pre>
<h4>MySQL monitoring</h4>
<p><a href="http://dev.mysql.com/news-and-events/newsletter/2004-01/a0000000301.html">
http://dev.mysql.com/news-and-events/newsletter/2004-01/a0000000301.html</a></p>
<p>mysqladmin extended (absolute values):</p>
<pre>mysqladmin extended -i10 | grep --color -i 'slave_running\|threads_connected\|threads_running'</pre>
<p>mysqladmin extended -i10 -r (relative values):</p>
<pre>mysqladmin extended -i10 -r | grep --color -i 'questions\|aborted_clients\|opened_tables\|slow_queries\|threads_created'</pre>
<h4>How to copy a database to another server</h4>
<p><em>On first server</em></p>
<ul>
<li>Dump the existing database
<pre>mysqldump -u<em>myusername</em> <em>mydb</em> > <em>mydump.sql</em></pre>
</li>
<li>Transfer the file
<pre>scp -i <em>mysshkeyfile</em> <em>mydump.sql</em> <em>user@XX.XXX.XXX.XX:/my/path</em></pre>
</li>
</ul>
<p><em>On the second server:</em></p>
<ul>
<li>Restore database:
<pre>mysql -u<em>myusername</em> <em>mydb</em> < <em>mydump.sql</em></pre>
</li>
</ul>
<h4>How to show the columns of a table</h4>
<pre>SHOW COLUMNS IN <em>mytable</em>;</pre>
<h4>How to delete a user</h4>
<a href="http://dev.mysql.com/doc/refman/5.0/en/delete.html">MySQL DELETE</a>
<pre>use mysql
delete from user where user = "myusertodelete" and host = "127.0.0.1"; </pre>
<h4>How to copy a table from one database to another</h4>
<ul>
<li><pre>mysql <em>mydb1</em>
SHOW CREATE TABLE <em>mytable</em>;</pre>
</li>
<li>Copy the output text, then run the exact same query with your destination database selected.
<pre>mysql <em>mydb2</em></pre>
<p>Then paste in the CREATE TABLE command from above.</p>
</li>
<li>To move the data, you will then need to:
<pre>mysql
INSERT INTO <em>mydb2.mytable</em> SELECT * FROM <em>mydb1.mytable</em>;</pre>
<p>From: <a href="http://www.experts-exchange.com/Databases/Mysql/Q_20973316.html">
http://www.experts-exchange.com/Databases/Mysql/Q_20973316.html</a></p>
</li>
</ul>
<h4>mysql update from another table</h4>
<a href="http://worcesterwideweb.com/2007/07/03/mysql-update-from-another-table/">
http://worcesterwideweb.com/2007/07/03/mysql-update-from-another-table/</a>
<pre>UPDATE updatefrom p, updateto pp
SET pp.last_name = p.last_name
WHERE pp.visid = p.id</pre>
<h4>How to dump table rows that match a WHERE clause</h4>
<a href="http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_where">Documentation</a>
<pre>mysqldump mydb mytable --where="mycol='myvalue'" > mydumpfile.mysql</pre>
<h4>How to purge the binary log</h4>
<ul>
<li><a href="http://dev.mysql.com/doc/refman/5.0/en/binary-log.html">http://dev.mysql.com/doc/refman/5.0/en/binary-log.html</a></li>
<li><a href="http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html">http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html</a></li>
</ul>
<h4>How to bind to localhost (don't allow connections from outside localhost)</h4>
<a href="http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_bind-address">http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_bind-address</a>
<a href="http://fudforum.org/forum/index.php?t=msg&goto=3026">http://fudforum.org/forum/index.php?t=msg&goto=3026</a>
Add <code>bind-address=127.0.0.1</code> in the <code>[mysqld]</code> section
of <code>/etc/mysql/my.cnf</code>.
<h4>How to determine if a table is using MyISAM or InnoDB engine</h4>
<a href="http://forums.mysql.com/read.php?28,132488,133090#msg-133090">http://forums.mysql.com/read.php?28,132488,133090#msg-133090</a>
<pre>SHOW TABLE STATUS;</pre>
<h4>mysqld does not start with upstart</h4>
http://www.uluga.ubuntuforums.org/showthread.php?p=9533623
https://bugs.launchpad.net/ubuntu/+source/mysql-dfsg-5.1/+bug/573318?comments=all
<p>This works:</p>
<pre>sudo -u mysql mysqld</pre>
This didn't work:
<pre>sudo service mysql start</pre>
<code>/etc/mysql/my.cnf</code> was missing.
<h4>Where are data files location (on Ubuntu)</h4>
<pre>/var/lib/mysql</pre>
<p>See <code>datadir</code> in <code>/etc/mysql/my.cnf</code></p>.
http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_datadir
<h4>How to show current mysql configuration variables</h4>
<pre>mysqladmin -u root variables</pre>
<h4>Can't connect to MySQL server error</h4>
<pre>ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)</pre>
<ul>
<li>Check <code>socket</code> location is the same for <code>[client]</code>
and <code>[mysqld]</code>
</li>
</ul>
<h4>Can't create test file error</h4>
<p>Trying to move datadir from /var/lib/mysql to /mnt/mysql-data on EC2</p>
<p><a href="http://ubuntuforums.org/showthread.php?t=782224">
http://ubuntuforums.org/showthread.php?t=782224</a>
</p>
<p>Had to edit <code>/etc/apparmor.d/usr.sbin.mysqld</code></p>
Notes on Django and MySql on Amazon's EC2
2008-08-30T03:08:25-07:00https://www.saltycrane.com/blog/2008/08/notes-django-and-mysql-amazons-ec2/<h5>Install Elasticfox</h5>
<p>Install the Elasticfox Firefox Extension for Amazon EC2:
<a href="http://developer.amazonwebservices.com/connect/entry.jspa?externalID=609">
http://developer.amazonwebservices.com/connect/entry.jspa?externalID=609</a>
</p>
<h5>Set up Amazon EC2 accounts and Elasticfox</h5>
<p>Follow
<a href="http://arope99.blogspot.com/2008/05/getting-started-with-amazon-elastic.html">
Arope's instructions for setting up Amazon EC2 accounts
and Elasticfox</a>. I used the
alestic/ubuntu-8.04-hardy-base-20080628.manifest.xml machine
image.
</p>
<h5>view standard apache page</h5>
<p>In Elasticfox, right-click on your running instance and select
"Copy Public DNS Name to clipboard". Then, paste that address
in your browser. You should see Apache's "It works!" page.
</p>
<h5>ssh into instance</h5>
<p>In Elasticfox, right-click on your running instance and select
"SSH to Public Domain Name"</p>
<h5>install stuff</h5>
<p>Ubuntu Hardy has the following versions:</p>
<ul>
<li>Apache 2.2.8</li>
<li>Mod_python 3.3.1</li>
<li>MySql 5.0.51</li>
<li>Django 0.96.1</li>
</ul>
<br>
<p>On your remote instance, do the following.</p>
<pre># apt-get update
# apt-get install python-django
# apt-get install mysql-server
# apt-get install python-mysqldb
# apt-get install libapache2-mod-python</pre>
<p><em>Update 2008-09-09</em>: The
<a href="http://www.djangoproject.com/documentation/modpython/">Django mod_python
documentation</a> recommends using Apache's
<a href ="http://httpd.apache.org/docs/2.2/mod/prefork.html">prefork MPM</a> as opposed
to the <a href="http://httpd.apache.org/docs/2.2/mod/worker.html">worker MPM</a>. The
worker MPM was installed by default on my Alestic Ubuntu image so I uninstalled it and replaced it
with the prefork version.</p>
<pre># apt-get autoremove --purge apache2-mpm-worker
# apt-get install apache2-mpm-prefork</pre>
<p>To see your current version of Apache, run the command:
<code>apache2 -V</code></p>
<h5>create a django project</h5>
<pre># cd /srv
# django-admin startproject mysite</pre>
<h5>configure django mod_python</h5>
<p>See also Jeff Baier's article:
<a href="http://www.jeffbaier.com/2007/07/26/installing-django-on-an-ubuntu-linux-server/">
Installing Django on an Ubuntu Linux Server</a>
for more information.
</p>
<p>Edit <code>/etc/apache2/httpd.conf</code> and insert the
following:</p>
<pre><location "/">
SetHandler python-program
PythonHandler django.core.handlers.modpython
SetEnv DJANGO_SETTINGS_MODULE mysite.settings
PythonPath "['/srv'] + sys.path"
PythonDebug On
</location></pre>
<h5>restart the apache server</h5>
<pre># /etc/init.d/apache2 restart</pre>
<p>You should see Django's "It Worked!" page.</p>
<h5>Set up a MySql database and user</h5>
<p>Note, use the password you entered when installing MySql</p>
<pre># mysql -u root -p
Enter password:
mysql> CREATE DATABASE django_db;
Query OK, 1 row affected (0.01 sec)
mysql> GRANT ALL ON django_db.* TO 'djangouser'@'localhost' IDENTIFIED BY 'yourpassword';
Query OK, 0 rows affected (0.03 sec)
mysql> quit
Bye</pre>
<h5>Edit the Django database settings</h5>
Edit <code>mysite/settings.py</code>:
<pre>DATABASE_ENGINE = 'mysql' # 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'ado_mssql'.
DATABASE_NAME = 'django_db' # Or path to database file if using sqlite3.
DATABASE_USER = 'djangouser' # Not used with sqlite3.
DATABASE_PASSWORD = 'yourpassword' # Not used with sqlite3.
DATABASE_HOST = '' # Set to empty string for localhost. Not used with sqlite3.
DATABASE_PORT = '' # Set to empty string for default. Not used with sqlite3.</pre>
<h5>Do a 'syncdb' to create the database tables</h5>
<pre># cd mysite
# python manage.py syncdb
Creating table auth_message
Creating table auth_group
Creating table auth_user
Creating table auth_permission
Creating table django_content_type
Creating table django_session
Creating table django_site
You just installed Django's auth system, which means you don't have any superusers defined.
Would you like to create one now? (yes/no): yes
Username (Leave blank to use 'sofeng'):
E-mail address: sofeng@email.com
Password:
Password (again):
Superuser created successfully.
Installing index for auth.Message model
Installing index for auth.Permission model
Loading 'initial_data' fixtures...
No fixtures found.</pre>
<h5>upload a mercurial django project</h5>
<p>on the remote instance, install mercurial:</p>
<pre># apt-get install mercurial</pre>
<p>on your local machine with the mercurial repo, run:</p>
<pre>$ hg clone -e 'ssh -i /home/sofeng/.ec2-elasticfox/id_django-keypair.pem' yourproj ssh://root@yourdns.compute-1.amazonaws.com//srv/yourproj</pre>
where <code>/home/sofeng/.ec2-elasticfox/id_django-keypair.pem</code> is
the private key associated with your instance and
<code>yourdns.compute-1.amazonaws.com</code> is the
public domain name associated with your instance.
<p>back on the remote instance:</p>
<pre># cd /srv/mozblog
# hg update</pre>
<pre># python manage.py syncdb</pre>
<h5>set up apache to serve static files</h5>
<ul>
<li>Create a link to the media files:
<pre># cd /var/www
# ln -s /srv/mozblog/media site_media
# ln -s /usr/share/python-support/python-django/django/contrib/admin/media/ admin_media</pre>
</li>
<li>Edit <code>/etc/apache2/httpd.conf</code>:
<pre><location "/">
SetHandler python-program
PythonHandler django.core.handlers.modpython
SetEnv DJANGO_SETTINGS_MODULE mozblog.settings
PythonPath "['/srv'] + sys.path"
PythonDebug On
</location>
<location "/site_media">
SetHandler None
</location>
<location "/admin_media">
SetHandler None
</location></pre>
</li>
</ul>
<h5>Restart the apache server</h5>
<pre># /etc/init.d/apache2 restart</pre>
<br>
Django Blog Project #10: Adding support for multiple authors
2008-08-01T12:38:01-07:00https://www.saltycrane.com/blog/2008/08/django-blog-project-10-adding-support-multiple-authors/<p>Here is a quick post on how I added support for multiple users on my blog.</p>
<h5>Modfiy the model</h5>
Excerpt from <code>~/src/django/myblogsite/myblogapp/models.py</code>:
<pre>import re
from django.db import models
<span style="color:red">from django.contrib.auth.models import User</span>
class Post(models.Model):
<span style="color:red">author = models.ForeignKey(User)</span>
title = models.CharField(maxlength=200)
slug = models.SlugField(maxlength=200,
prepopulate_from=['title'],
unique_for_month='date_created')
date_created = models.DateTimeField(auto_now_add=True)
date_modified = models.DateTimeField(auto_now=True)
tags = models.CharField(maxlength=200, help_text="Space separated.")
body = models.TextField()
body_html = models.TextField(editable=False, blank=True)
lc_count = models.IntegerField(default=0, editable=False)
def get_tag_list(self):
return re.split(" ", self.tags)
def get_absolute_url(self):
return "/blog/%d/%02d/%s/" % (self.date_created.year,
self.date_created.month,
self.slug)
def __str__(self):
return self.title
class Meta:
ordering = ["-date_created"]
class Admin:
pass</pre>
<h5>Update the database</h5>
<ul>
<li>List the SQL commands Django would use the create the database tables:
<pre>$ cd ~/src/django/myblogsite/
$ python manage.py sqlall myblogapp</pre>
<pre>BEGIN;
CREATE TABLE "myblogapp_post" (
"id" integer NOT NULL PRIMARY KEY,
"author_id" integer NOT NULL REFERENCES "auth_user" ("id"),
"title" varchar(200) NOT NULL,
"slug" varchar(200) NOT NULL,
"date_created" datetime NOT NULL,
"date_modified" datetime NOT NULL,
"tags" varchar(200) NOT NULL,
"body" text NOT NULL,
"body_html" text NOT NULL,
"lc_count" integer NOT NULL
);
CREATE INDEX myblogapp_post_author_id ON "myblogapp_post" ("author_id");
CREATE INDEX myblogapp_post_slug ON "myblogapp_post" ("slug");
COMMIT;</pre>
</li>
<li>Enter the sqlite shell:
<pre>$ sqlite3 mydatabase.sqlite3</pre>
<br>
and enter the following statement:
<pre>sqlite> ALTER TABLE myblogapp_post ADD COLUMN author_id integer REFERENCES auth_user (id);
sqlite> .exit</pre>
</li>
</ul>
<h5>Update the template</h5>
Excerpt from <code>~/src/django/myblogsite/templates/singlepost.html</code>:
<pre> <h3>{{ post.title }}</h3>
{{ post.body }}
<hr>
<div class="post_footer">
<span style="color:red">Author: {{ post.author.first_name }}<br></span>
Date created: {{ post.date_created.date }}<br>
{% ifnotequal post.date_modified.date post.date_created.date %}
Last modified: {{ post.date_modified.date }}<br>
{% endifnotequal %}
Tags:
{% for tag in post.get_tag_list %}
<a href="/blog/tag/{{ tag }}/">{{ tag }}</a>{% if not forloop.last %}, {% endif %}
{% endfor %}
<br>
<a href="/admin/myblogapp/post/{{ post.id }}">Edit post</a>
</div></pre>
<p>Now you should be able to go in to the Admin interface select a
user to associate with each post. Unfortunately, it does not automatically
associate the logged in user with the post.</p>
<p>Here is a snapshot screenshot of what I'm calling version 0.1.1.
Yeah, I know, I skipped 0.1.0-- I consider that to be the point
where I said
<a href="http://www.saltycrane.com/blog/2008/07/transition-bye-blogger-boing/">
Goodbye Blogger</a> and
<a href="http://www.saltycrane.com/blog/2008/07/hello-saltycrane/">Hello
Saltycrane</a>.
</p>
<img align="center" src="/site_media/image/version0.1.1.png">
How to set up Django with MySql on Ubuntu Hardy
2008-07-30T15:09:21-07:00https://www.saltycrane.com/blog/2008/07/how-set-django-mysql-ubuntu-hardy/<p>Here are my notes on installing Django with MySql. Almost all
of this was taken from Zeth's article:
<a href="http://commandline.org.uk/python/2008/jan/25/baby-steps-with-django-part-2-database-setup/">
Baby Steps with Django - part 2 database setup</a>.
</p>
<h5>Install Django and MySql</h5>
<p>Note: during the installation of mysql-server, you will be
prompted for a root password. Use this in the section below.
</p>
<pre>$ sudo apt-get install python-django
$ sudo apt-get install mysql-server
$ sudo apt-get install python-mysqldb</pre>
<h5>Set up a MySql database and user</h5>
<p>Note, use the password you entered when installing MySql</p>
<pre>$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51a-3ubuntu5.1 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE DATABASE django_db;
Query OK, 1 row affected (0.01 sec)
mysql> GRANT ALL ON django_db.* TO 'djangouser'@'localhost' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.03 sec)
mysql> quit
Bye</pre>
<h5>Create a Django Project</h5>
<pre>$ django-admin startproject mysite</pre>
<h5>Edit the Django database settings</h5>
Edit <code>mysite/settings.py</code>:
<pre>DATABASE_ENGINE = 'mysql' # 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'ado_mssql'.
DATABASE_NAME = 'django_db' # Or path to database file if using sqlite3.
DATABASE_USER = 'djangouser' # Not used with sqlite3.
DATABASE_PASSWORD = 'mypassword' # Not used with sqlite3.
DATABASE_HOST = '' # Set to empty string for localhost. Not used with sqlite3.
DATABASE_PORT = '' # Set to empty string for default. Not used with sqlite3.</pre>
<h5>Use Django to create the database tables</h5>
<pre>$ cd mysite
$ python manage.py syncdb
Creating table auth_message
Creating table auth_group
Creating table auth_user
Creating table auth_permission
Creating table django_content_type
Creating table django_session
Creating table django_site
You just installed Django's auth system, which means you don't have any superusers defined.
Would you like to create one now? (yes/no): yes
Username (Leave blank to use 'sofeng'):
E-mail address: sofeng@email.com
Password:
Password (again):
Superuser created successfully.
Installing index for auth.Message model
Installing index for auth.Permission model
Loading 'initial_data' fixtures...
No fixtures found.</pre>
<h5>Run the development server</h5>
<pre>$ python manage.py runserver
Validating models...
0 errors found.
Django version 0.96.1, using settings 'mysite.settings'
Development server is running at http://127.0.0.1:8000/
Quit the server with CONTROL-C.
[30/Jul/2008 16:37:23] "GET / HTTP/1.1" 404 2053</pre>
<p>Point your browser at <code>http://127.0.0.1:8000</code> and
you should see the Django It worked! page.</p>
Recommended books
2008-04-17T19:13:00-07:00https://www.saltycrane.com/blog/2008/04/recommended-books/<p>I love having a subscription to <a href="http://search.safaribooksonline.com/home">
Safari Books Online</a>. Currently my company provides a free subscription,
but if I get a new job, I might consider subscribing myself. Since I get to
browse a number of books at no cost, I thought I'd note which books are my
favorites. (Note, I am not being paid by Safari Books Online.)</p>
<h4 id="general-software">General Software</h4>
<ul>
<li><em>Structure and Interpretation of Computer Programs, Second Edition</em>,
Harold Abelson and Gerald Jay Sussman, MIT Press, ?year?<br />
I learned about this book through a job posting. It might bring you to
tears if you get it. I'm only in the second chapter. It is used in an introductory
Computer Science course at MIT. It uses Scheme (Lisp) to demonstrate concepts.<br />
Available free online at:
<a href="http://mitpress.mit.edu/sicp/full-text/book/book.html">
http://mitpress.mit.edu/sicp/full-text/book/book-Z-H-4.html</a><br />
A <a href="http://swiss.csail.mit.edu/classes/6.001/abelson-sussman-lectures/">
free video lecture series</a> is also available.
</li>
</ul>
<h4 id="c">C</h4>
<ul>
<li><em>The C Programming Language, Second Edition</em>, Brian W. Kernighan
and Dennis M. Ritchie, Prentice Hall, 1988<br />
The definitive C book.
</li>
</ul>
<h4 id="python">Python</h4>
<ul>
<li><em>Core Python Programming, Second Edition</em>, Wesley J. Chun,
Prentice Hall, September 18, 2006<br />
Usually I like O'Reilly books best, but I slightly prefer Chun's text
to <em>Learning Python</em>.<br />
Available at
<a href="http://search.safaribooksonline.com/0132269937?tocview=true">
Safari Books Online</a>
</li>
<li><em>The Django Book</em>, Apress, December 2007<br />
I think this is the first official Django book.<br />
</li>
</ul>
<h4 id="sqlite">SQLite</h4>
<ul>
<li><em>The Definitive Guide to SQLite</em>, Mike Owens, Apress, May 2006<br />
I browsed a few SQL books but liked this one better than most.
It has a good theory section.<br />
Available at
<a href="http://apress.com/book/view/9781590596739">
Apress.com</a>
</li>
</ul>
<h4 id="linux">Linux or related</h4>
<ul>
<li><em>X Power Tools</em>, Chris Tyler, O'Reilly, December 15, 2007<br />
Lots of good information on the X Window System and more; easy to understand.
I wish the basic Ubuntu or
Linux books had some of this information.<br />
Available at
<a href="http://search.safaribooksonline.com/9780596101954?tocview=true">
Safari Books Online</a>
</li>
<li>
<em>SSH, The Secure Shell: The Definitive Guide, Second Edition</em>,
Daniel J. Barrett; Richard E. Silverman; Robert G. Byrnes,
O'Reilly, May 10, 2005<br>
Available at
<a href="http://search.safaribooksonline.com/book/networking/ssh/0596008953">
Safari Books Online</a>
</li>
</ul>
<h4 id="ruby">Ruby</h4>
<ul>
<li><em>why's poignant guide to Ruby</em> by <a href="http://whytheluckystiff.net/">why
the lucky stiff</a><br />
Only in chapter 3, but very funny.<br />
Available free online at: <a href="http://poignantguide.net/ruby/">http://poignantguide.net/ruby/</a>
</li>
</ul>
<h4 id="javascript">Javascript</h4>
<ul>
<li>
<em>Javascript: The Good Parts</em>, Douglas Crockford, O'Reilly, May 2008<br>
This is the first Javascript book I read as I switched to Javascript and Frontend development. I was debating whether to read it since it was so old but I found it to be good and recommend it.
</li>
<li>
<em>You Don't Know JS: ES6 & Beyond</em>, Kyle Simpson, O'Reilly, December 2015<br>
This is the primary way I learned ES6. It is very detail oriented.<br>
Available free online
<a href="https://github.com/getify/You-Dont-Know-JS/tree/master/es6%20%26%20beyond">
on github
</a>
</li>
<li>
<em>JavaScript Allongé, the "Six" Edition</em>, Reg "raganwald" Braithwaite, Leanpub, 2016<br>
This was the third Javascript book I read and it was the most fun. It teaches functional programming concepts such as closures, shadowing, higher order functions, combinators, decorators, etc using ES6.<br>
Available free online at:
<a href="https://leanpub.com/javascriptallongesix/read">https://leanpub.com/javascriptallongesix/read</a>
</li>
</ul>
<h4 id="non-technical">Non technical</h4>
<ul>
<li>The Hitchhiker's Guide to the Galaxy by Douglas Adams</li>
<li>Calvin and Hobbes by Bill Watterson</li>
<li>Crime and Punishment by Fyodor Dostoyevsky</li>
<li>Mere Christianity by C. S. Lewis</li>
<li>Screwtape Letters by C. S. Lewis</li>
</ul>
<h4 id="see-also">See also</h4>
<ul>
<li><a href="http://www.reddit.com/r/books/comments/ch0wt/a_reading_list_for_the_selftaught_computer/">
http://www.reddit.com/r/books/comments/ch0wt/a_reading_list_for_the_selftaught_computer/</a></li>
<li><a href="http://stackoverflow.com/questions/1711/what-is-the-single-most-influential-book-every-programmer-should-read">
http://stackoverflow.com/questions/1711/what-is-the-single-most-influential-book-every-programmer-should-read</a></li>
<li><a href="http://www.reddit.com/r/books/comments/cq4qe/reddits_bookshelf/">
http://www.reddit.com/r/books/comments/cq4qe/reddits_bookshelf/</a></li>
</ul>
Migrating Excel data to SQLite using Python
2007-11-27T18:13:00-08:00https://www.saltycrane.com/blog/2007/11/migrating-excel-to-sqlite-using-python/<p>In
a <a href="http://www.saltycrane.com/blog/2007/11/python-sqlite-tutorial-relational/">previous
post</a>, I described how I designed a SQLite relational database
from an Excel table. It was a small example, so I hardcoded the data
into the Python script. For my actual problem, I need to convert my
Excel data into a SQLite database automatically. To do this, I used
the <code>win32com</code> module and the <code>sqlite3</code> module
included in Python 2.5.</p>
<p>Here is the table from my previous post. It shows some variables in
in my C program. It shows the variable name, type, the module it belongs to,
and a short description. Here is the table from my previous post. It shows some variables in
in my C program. It shows the variable name, type, the module it belongs to,
and a short description.
</p>
<table style="font-family: arial;
font-size: x-small;
background-color: #ffffff;">
<tr style="background-color:aaaaaa">
<td style="padding: 2px; background-color: #aaaaaa">id</td>
<td style="padding: 2px; background-color: #aaaaaa">name</td>
<td style="padding: 2px; background-color: #aaaaaa">module</td>
<td style="padding: 2px; background-color: #aaaaaa">type</td>
<td style="padding: 2px; background-color: #aaaaaa">desc</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">1</td>
<td style="padding: 2px; background-color: #dddddd">foo</td>
<td style="padding: 2px; background-color: #dddddd">ModuleExt</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of foo</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">2</td>
<td style="padding: 2px; background-color: #dddddd">bar</td>
<td style="padding: 2px; background-color: #dddddd">ModuleExt</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of bar</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">3</td>
<td style="padding: 2px; background-color: #dddddd">knark</td>
<td style="padding: 2px; background-color: #dddddd">Module1</td>
<td style="padding: 2px; background-color: #dddddd">int</td>
<td style="padding: 2px; background-color: #dddddd">Description of knark</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">4</td>
<td style="padding: 2px; background-color: #dddddd">wert</td>
<td style="padding: 2px; background-color: #dddddd">Module1</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of wert</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">5</td>
<td style="padding: 2px; background-color: #dddddd">jib</td>
<td style="padding: 2px; background-color: #dddddd">Module1</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of jib</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">6</td>
<td style="padding: 2px; background-color: #dddddd">laz</td>
<td style="padding: 2px; background-color: #dddddd">Module2</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of laz</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">7</td>
<td style="padding: 2px; background-color: #dddddd">kew</td>
<td style="padding: 2px; background-color: #dddddd">Module2</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of kew</td>
</tr>
</table>
<p>After installing the <code>win32com</code> module
from <a href="http://sourceforge.net/project/platformdownload.php?group_id=78018">http://sourceforge.net/project/platformdownload.php?group_id=78018</a>,
I used the following code.</p>
<pre class="python">import os
import sqlite3
from win32com.client import constants, Dispatch
#----------------------------------------
# get data from excel file
#----------------------------------------
XLS_FILE = os.getcwd() + "\\example.xls"
ROW_SPAN = (14, 21)
COL_SPAN = (2, 7)
app = Dispatch("Excel.Application")
app.Visible = True
ws = app.Workbooks.Open(XLS_FILE).Sheets(1)
exceldata = [[ws.Cells(row, col).Value
for col in xrange(COL_SPAN[0], COL_SPAN[1])]
for row in xrange(ROW_SPAN[0], ROW_SPAN[1])]
#----------------------------------------
# create SQL table and fill it with data
#----------------------------------------
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute('''CREATE TABLE exceltable (
id INTEGER,
name TEXT,
module TEXT,
type TEXT,
desc TEXT
)''')
for row in exceldata:
c.execute('INSERT INTO exceltable VALUES (?,?,?,?,?)', row)
conn.commit()
#----------------------------------------
# display SQL data
#----------------------------------------
c.execute('SELECT * FROM exceltable')
for row in c:
print row</pre>
<p>The Excel filename is <em>example.xls</em> and the table data
begins at B14 (2nd column, 14th row) and ends at F20 (6th column,
20th row) in Sheet 1. The script uses a declarative approach to
store the data in a Python list of lists. It creates
a SQLite database named <em>example.db</em> and creates a connection to
it. It then fills the database using the Python list data
structure. Finally, it displays the newly created database. The
screen output is shown below.</p>
<pre>(1, u'foo', u'ModuleExt', u'double', u'Description of foo')
(2, u'bar', u'ModuleExt', u'double', u'Description of bar')
(3, u'knark', u'Module1', u'int', u'Description of knark')
(4, u'wert', u'Module1', u'double', u'Description of wert')
(5, u'jib', u'Module1', u'double', u'Description of jib')
(6, u'laz', u'Module2', u'double', u'Description of laz')
(7, u'kew', u'Module2', u'double', u'Description of kew')</pre>
<p>If I want to interact with the newly created database using SQLite
directly, I can run <kbd>sqlite3 example.db</kbd> from my Cygwin
bash command line. (Note the <code>conn.commit()</code> line in my
Python script is very important for this step to work. For some
reason, I didn't see this in the Python documentation.) Here is an
example session using SQLite directly with the new database.</p>
<pre>$ sqlite3 example.db
SQLite version 3.5.1
Enter ".help" for instructions
sqlite> .schema
CREATE TABLE exceltable (
id INTEGER,
name TEXT,
module TEXT,
type TEXT,
desc TEXT
);
sqlite> select * from exceltable;
1|foo|ModuleExt|double|Description of foo
2|bar|ModuleExt|double|Description of bar
3|knark|Module1|int|Description of knark
4|wert|Module1|double|Description of wert
5|jib|Module1|double|Description of jib
6|laz|Module2|double|Description of laz
7|kew|Module2|double|Description of kew
sqlite></pre>
Django project #2: SQLite setup
2007-11-15T00:26:00-08:00https://www.saltycrane.com/blog/2007/11/django-project-2-sqlite-setup/
<p>In the <a href="http://www.saltycrane.com/blog/2007/11/django-project-1-install/">first installment</a> of the <em>Sample Django Project</em>, I installed Django and created a project. In this installment, I will set up the SQLite database. At first, I thought I had to figure out what kind of data to put in the database, but in actuality, I can create an empty database and fill it in later. That is what I am doing. Here are the steps.</p>
<ol>
<li>Install SQLite 3
<pre>sofeng@tortoise:~$ sudo apt-get install sqlite3</pre>
</li>
<li>Edit settings.py<br />
<kbd>cd</kbd> to the <em>mysite</em> directory created last time.
<pre>sofeng@tortoise:~$ cd ~/Web/mysite</pre>
Edit <code>settings.py</code> and change the following 2 lines
<pre>DATABASE_ENGINE = 'sqlite3'
DATABASE_NAME = '/home/sofeng/Web/mysite/mydb'</pre>
</li>
<li>Create Django tables in the database
<pre>sofeng@tortoise:~/Web/mysite$ python manage.py syncdb
Creating table auth_message
Creating table auth_group
Creating table auth_user
Creating table auth_permission
Creating table django_content_type
Creating table django_session
Creating table django_site
You just installed Django's auth system, which means you don't have any superusers defined.
Would you like to create one now? (yes/no): yes
Username (Leave blank to use 'sofeng'):
E-mail address: youremail@yourhost.com
Password:
Password (again):
Superuser created successfully.
Installing index for auth.Message model
Installing index for auth.Permission model
Loading 'initial_data' fixtures...
No fixtures found.</pre>
</li>
<li>Take a look at the databases created
<pre>sofeng@tortoise:~/Web/mysite$ sqlite3 mydb
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> .schema</pre>
You should see a bunch of CREATE TABLE statements. If you get the following error message, it probably means you used sqlite instead of sqlite3.
<pre>Unable to open database "mydb": file is encrypted or is not a database</pre>
</li>
</ol>
<p>Well that was pretty easy. Next time, we'll create some models and actually write some python code.</p>
Relational database introduction with Python and SQLite
2007-11-14T17:37:00-08:00https://www.saltycrane.com/blog/2007/11/python-sqlite-tutorial-relational/<p>I mentioned in a <a href="http://www.saltycrane.com/blog/2007/09/python-word-wrap-function/">previous post</a> that I have an Excel spreadsheet containing a bunch of information about the development C code I'm working on. It is a large table showing which variables are input and output from which functions. The variable names are in the first column and the function names are in the last several columns of the first row. I use "i" or "o" to denote if a variable is an input to or an output from a particular function. I also have a few columns for the variable type and description. A simplified example is shown in the table below.</p>
<strong style="font-family: arial; font-size: small; text-align: right">ORIGINAL TABLE</strong><br />
<table style="font-family: arial;
font-size: x-small;
background-color: #ffffff;">
<tr style="background-color:aaaaaa">
<td style="padding: 2px; background-color: #aaaaaa">name</td>
<td style="padding: 2px; background-color: #aaaaaa">module</td>
<td style="padding: 2px; background-color: #aaaaaa">type</td>
<td style="padding: 2px; background-color: #aaaaaa">desc</td>
<td style="padding: 2px; background-color: #aaaaaa">ExtSource</td>
<td style="padding: 2px; background-color: #aaaaaa">DoThis</td>
<td style="padding: 2px; background-color: #aaaaaa">CalcThis</td>
<td style="padding: 2px; background-color: #aaaaaa">CalcThat</td>
<td style="padding: 2px; background-color: #aaaaaa">ModifyStuff</td>
<td style="padding: 2px; background-color: #aaaaaa">ExtSink</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">foo</td>
<td style="padding: 2px; background-color: #dddddd">ModuleExt</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of foo</td>
<td style="padding: 2px; background-color: #dddddd">o</td>
<td style="padding: 2px; background-color: #dddddd">i</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd">i</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd"> </td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">bar</td>
<td style="padding: 2px; background-color: #dddddd">ModuleExt</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of bar</td>
<td style="padding: 2px; background-color: #dddddd">o</td>
<td style="padding: 2px; background-color: #dddddd">i</td>
<td style="padding: 2px; background-color: #dddddd">i</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd"> </td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">knark</td>
<td style="padding: 2px; background-color: #dddddd">Module1</td>
<td style="padding: 2px; background-color: #dddddd">int</td>
<td style="padding: 2px; background-color: #dddddd">Description of knark</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd">io</td>
<td style="padding: 2px; background-color: #dddddd">i</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd"> </td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">wert</td>
<td style="padding: 2px; background-color: #dddddd">Module1</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of wert</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd">o</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd">i</td>
<td style="padding: 2px; background-color: #dddddd">i</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">jib</td>
<td style="padding: 2px; background-color: #dddddd">Module1</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of jib</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd">o</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd">i</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">laz</td>
<td style="padding: 2px; background-color: #dddddd">Module2</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of laz</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd">o</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd">o</td>
<td style="padding: 2px; background-color: #dddddd">i</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">kew</td>
<td style="padding: 2px; background-color: #dddddd">Module2</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of kew</td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd"> </td>
<td style="padding: 2px; background-color: #dddddd">o</td>
<td style="padding: 2px; background-color: #dddddd">o</td>
<td style="padding: 2px; background-color: #dddddd">i</td>
</tr>
</table>
<p>As shown above, the table also includes the variable descriptions, type, and scope. In my real, unsimplified Excel table, I also include function-related information such as the function description, and the function prototype. This additional information makes the table very messy. Try to imagine where you would put the function description and function prototype information in the table above. I came up with a kludge solution, but it involved repeated information, and rows and columns with inconsistent meaning. I was realizing the limitations of a 2-dimensional table.</p>
<p>I had been interested in learning about relational databases, so this seemed like a good opportunity. Relational databases imply the SQL query language-- relational databases that use SQL are about the only kind around. They include: Oracle, Microsoft SQL Server, PostgreSQL, MySQL, and SQLite among others. I chose SQLite for my task because it is lightweight (no separate server), free, popular, and fast for small databases. (See <a href="http://www.sqlite.org/different.html">http://www.sqlite.org/different.html</a> for other distinctive features.) Also Python 2.5 now includes an interface module, <code>sqlite3</code>, as part of the standard distribution. See <a href="http://docs.python.org/lib/module-sqlite3.html">http://docs.python.org/lib/module-sqlite3.html</a> for the documentation.</p>
<h4>Relational model design</h4>
<p>I could create a relational database using my original table in the Excel spreadsheet. However, this organization is not in the spirit of relational database design. The [relational] approach is to create many smaller tables and relationships between those tables. For my example, I created a <em>variable</em> table, a <em>function</em> table, and a <em>variable_function</em> table to indicate the relationship between the variable and function tables. Though it looks more complicated than the original single table form, inputting this into a relational database, such as SQLite, will allow for more advanced manipulation of the data. It is also much more [conducive] to maintenance and expansion. For example, now there is a natural place to put the additional function-related information which was so clumsy in the original single table. To do this, I add <em>desc</em> and <em>prototype</em> columns to the <em>function</em> table. If I needed to add more information to about the different modules, I could create a separate <em>module</em> table and include the module_id as a column in the <em>variable</em> table.</p>
<strong style="font-family: arial; font-size: small; text-align: right">VARIABLE TABLE</strong><br />
<table style="font-family: arial;
font-size: x-small;
background-color: #ffffff;">
<col>
</col><tr style="background-color:aaaaaa">
<td style="padding: 2px; background-color: #aaaaaa">id</td>
<td style="padding: 2px; background-color: #aaaaaa">name</td>
<td style="padding: 2px; background-color: #aaaaaa">module</td>
<td style="padding: 2px; background-color: #aaaaaa">type</td>
<td style="padding: 2px; background-color: #aaaaaa">desc</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">1</td>
<td style="padding: 2px; background-color: #dddddd">foo</td>
<td style="padding: 2px; background-color: #dddddd">ModuleExt</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of foo</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">2</td>
<td style="padding: 2px; background-color: #dddddd">bar</td>
<td style="padding: 2px; background-color: #dddddd">ModuleExt</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of bar</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">3</td>
<td style="padding: 2px; background-color: #dddddd">knark</td>
<td style="padding: 2px; background-color: #dddddd">Module1</td>
<td style="padding: 2px; background-color: #dddddd">int</td>
<td style="padding: 2px; background-color: #dddddd">Description of knark</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">4</td>
<td style="padding: 2px; background-color: #dddddd">wert</td>
<td style="padding: 2px; background-color: #dddddd">Module1</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of wert</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">5</td>
<td style="padding: 2px; background-color: #dddddd">jib</td>
<td style="padding: 2px; background-color: #dddddd">Module1</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of jib</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">6</td>
<td style="padding: 2px; background-color: #dddddd">laz</td>
<td style="padding: 2px; background-color: #dddddd">Module2</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of laz</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">7</td>
<td style="padding: 2px; background-color: #dddddd">kew</td>
<td style="padding: 2px; background-color: #dddddd">Module2</td>
<td style="padding: 2px; background-color: #dddddd">double</td>
<td style="padding: 2px; background-color: #dddddd">Description of kew</td>
</tr>
</table>
<br />
<strong style="font-family: arial; font-size: small; text-align: right">FUNCTION TABLE</strong><br />
<table style="font-family: arial;
font-size: x-small;
background-color: #ffffff;">
<tr style="background-color:aaaaaa"><td style="padding: 2px; background-color: #aaaaaa">id</td><td style="padding: 2px; background-color: #aaaaaa">name</td></tr>
<tr style="background-color:dddddd"><td style="padding: 2px; background-color: #dddddd">1</td><td style="padding: 2px; background-color: #dddddd">ExtSource</td></tr>
<tr style="background-color:dddddd"><td style="padding: 2px; background-color: #dddddd">2</td><td style="padding: 2px; background-color: #dddddd">DoThis</td></tr>
<tr style="background-color:dddddd"><td style="padding: 2px; background-color: #dddddd">3</td><td style="padding: 2px; background-color: #dddddd">CalcThis</td></tr>
<tr style="background-color:dddddd"><td style="padding: 2px; background-color: #dddddd">4</td><td style="padding: 2px; background-color: #dddddd">CalcThat</td></tr>
<tr style="background-color:dddddd"><td style="padding: 2px; background-color: #dddddd">5</td><td style="padding: 2px; background-color: #dddddd">ModifyStuff</td></tr>
<tr style="background-color:dddddd"><td style="padding: 2px; background-color: #dddddd">6</td><td style="padding: 2px; background-color: #dddddd">ExtSink</td></tr>
</table>
<br />
<strong style="font-family: arial; font-size: small; text-align: right">VARIABLE_FUNCTION TABLE</strong><br />
<table style="font-family: arial;
font-size: x-small;
background-color: #ffffff;">
<tr style="background-color:aaaaaa">
<td style="padding: 2px; background-color: #aaaaaa">variable_id</td>
<td style="padding: 2px; background-color: #aaaaaa">function_id</td>
<td style="padding: 2px; background-color: #aaaaaa">type</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">1</td>
<td style="padding: 2px; background-color: #dddddd">1</td>
<td style="padding: 2px; background-color: #dddddd">output</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">1</td>
<td style="padding: 2px; background-color: #dddddd">2</td>
<td style="padding: 2px; background-color: #dddddd">input</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">1</td>
<td style="padding: 2px; background-color: #dddddd">4</td>
<td style="padding: 2px; background-color: #dddddd">input</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">2</td>
<td style="padding: 2px; background-color: #dddddd">1</td>
<td style="padding: 2px; background-color: #dddddd">output</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">2</td>
<td style="padding: 2px; background-color: #dddddd">2</td>
<td style="padding: 2px; background-color: #dddddd">input</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">2</td>
<td style="padding: 2px; background-color: #dddddd">3</td>
<td style="padding: 2px; background-color: #dddddd">input</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">3</td>
<td style="padding: 2px; background-color: #dddddd">2</td>
<td style="padding: 2px; background-color: #dddddd">input-output</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">3</td>
<td style="padding: 2px; background-color: #dddddd">3</td>
<td style="padding: 2px; background-color: #dddddd">input</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">4</td>
<td style="padding: 2px; background-color: #dddddd">2</td>
<td style="padding: 2px; background-color: #dddddd">output</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">4</td>
<td style="padding: 2px; background-color: #dddddd">4</td>
<td style="padding: 2px; background-color: #dddddd">input</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">4</td>
<td style="padding: 2px; background-color: #dddddd">5</td>
<td style="padding: 2px; background-color: #dddddd">input</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">5</td>
<td style="padding: 2px; background-color: #dddddd">3</td>
<td style="padding: 2px; background-color: #dddddd">output</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">5</td>
<td style="padding: 2px; background-color: #dddddd">5</td>
<td style="padding: 2px; background-color: #dddddd">input</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">6</td>
<td style="padding: 2px; background-color: #dddddd">3</td>
<td style="padding: 2px; background-color: #dddddd">output</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">6</td>
<td style="padding: 2px; background-color: #dddddd">5</td>
<td style="padding: 2px; background-color: #dddddd">output</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">6</td>
<td style="padding: 2px; background-color: #dddddd">6</td>
<td style="padding: 2px; background-color: #dddddd">input</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">7</td>
<td style="padding: 2px; background-color: #dddddd">4</td>
<td style="padding: 2px; background-color: #dddddd">output</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">7</td>
<td style="padding: 2px; background-color: #dddddd">5</td>
<td style="padding: 2px; background-color: #dddddd">output</td>
</tr>
<tr style="background-color:dddddd">
<td style="padding: 2px; background-color: #dddddd">7</td>
<td style="padding: 2px; background-color: #dddddd">6</td>
<td style="padding: 2px; background-color: #dddddd">input</td>
</tr>
</table>
<h4>SQLite Implementation (w/ Python)</h4>
<p>To implement this database, all I needed was Python 2.5. If you have an older version of Python, you can install the pysqlite module. Here is the Python code to implement the database above.
<pre class="python">import sqlite3
# data
VARIABLES = (
(1, 'foo', 'ModuleExt', 'double', 'Description of foo'),
(2, 'bar', 'ModuleExt', 'double', 'Description of bar'),
(3, 'knark', 'Module1', 'int', 'Description of knark'),
(4, 'wert', 'Module1', 'double', 'Description of wert'),
(5, 'jib', 'Module1', 'double', 'Description of jib'),
(6, 'laz', 'Module2', 'double', 'Description of laz'),
(7, 'kew', 'Module2', 'double', 'Description of kew'),
)
FUNCTIONS = (
(1, 'ExtSource'),
(2, 'DoThis'),
(3, 'CalcThis'),
(4, 'CalcThat'),
(5, 'ModifyStuff'),
(6, 'ExtSink'),
)
VAR_FUNC = (
(1, 1, 'output'),
(1, 2, 'input'),
(1, 4, 'input'),
(2, 1, 'output'),
(2, 2, 'input'),
(2, 3, 'input'),
(3, 2, 'input-output'),
(3, 3, 'input'),
(4, 2, 'output'),
(4, 4, 'input'),
(4, 5, 'input'),
(5, 3, 'output'),
(5, 5, 'input'),
(6, 3, 'output'),
(6, 5, 'output'),
(6, 6, 'input'),
(7, 4, 'output'),
(7, 5, 'output'),
(7, 6, 'input'),
)
# get connection and cursor objects
conn = sqlite3.connect('iodatabase')
c = conn.cursor()
# create tables
c.execute('''create table variable (
id integer,
name text,
module text,
type text,
desc text
)''')
c.execute('''create table function (
id integer,
name text
)''')
c.execute('''create table var_func (
variable_id integer,
function_id integer,
type text
)''')
# fill tables with data
for row in VARIABLES:
c.execute('insert into variable values (?,?,?,?,?)', row)
for row in FUNCTIONS:
c.execute('insert into function values (?,?)', row)
for row in VAR_FUNC:
c.execute('insert into var_func values (?,?,?)', row)</pre>
<h4>Querying the Database</h4>
</p><p>Now that I have created the database, I can get answers to interesting questions, such as <em>What variables are output by CalcThis?</em> Here is the Python/SQLite code to answer this question:
<pre class="python">c.execute(''.join([
'SELECT variable.name, variable.module, variable.type, variable.desc ',
'FROM variable, var_func, function ',
'WHERE variable.id=var_func.variable_id ',
'AND function.id=var_func.function_id ',
'AND function.name="CalcThis" ',
'AND var_func.type="output" ',
]))
FORMAT = '%-6s%-10s%-8s%-20s'
print FORMAT % ('name', 'module', 'type', 'desc')
print '-' * 44
for row in c:
print FORMAT % row</pre>
Here is the output.
<pre>name module type desc
----------------------------------------------
jib Module1 double Description of jib
laz Module2 double Description of laz</pre>
I can see this is consistent with my original table. The query works. For such a small example, the original method may seem easier, but as the number of entries grows, the benefit of the relational database grows as well. Here is another example which asks <em>Which functions use the variable wert as an input?</em>
<pre class="python">c.execute(''.join([
'SELECT function.name ',
'FROM variable, var_func, function ',
'WHERE variable.id=var_func.variable_id ',
'AND function.id=var_func.function_id ',
'AND variable.name="wert" ',
'AND var_func.type="input" ',
]))
print 'name'
print '------------'
for row in c:
print '%s' % row</pre>
Output:
<pre>name
---------
CalcThat
ModifyStuff</pre>
I would like to do even more complicated things like determine the prerequisite input variables across all functions for a given output variable. However, I still need to read more about that, so that will have to wait.
</p>