Geek Answers Handbook

Learn SQL The Hard way - Creating .sql with .db in SQL Lite 3 - Why and How?

As a novice programmer with 20-hour Python coding and familiarity with the command line, I opened Zed Shaw's “Learn SQL The Hard Way” and was quickly puzzled.

In exercise 01 , Zed created the first table with this first command:

sqlite3 ex1.db < ex1.sql

However, this cannot be done on my command line with the error message: "bash: ex1.sql: there is no such file or directory." I initially ignored this recommended code and continued:

 sqlite3 ex1.db SQLite version 3.7.15.1 2012-12-19 20:39:10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE person ( ...> id INTEGER PRIMARY KEY, ...> first_name TEXT, ...> last_name TEXT, ...> age INTEGER ...> );

Running "ls -l" on the command line shows:

 -rw-r--r-- 1 thefifth staff 2048 Feb 15 15:23 ex1.db

But I want and cannot get:

 $ ls -l -rw-r--r-- 1 zedshaw staff 2048 Nov 8 16:18 ex1.db -rw-r--r-- 1 zedshaw staff 92 Nov 8 16:14 ex1.sql

I figured out and found this blog that implements the same syntax "name.db <name.sql", but after that the code didn’t work here either. This Stack Overflow also has a similar syntax, but in the context of converting .sql to sqlite3.

In particular, I am wondering if this is a "<" for use in the native bash terminal and that I do not meet certain criteria for its proper use. Also, I don’t know the purpose of creating the .sql and .db file, although apparently it is much smaller than the other. I may have installed sqlite3 incorrectly, but it seems to be working fine.

Thank you for your help!

+6
source share
3 answers
  • Save the code in a file with the extension .sql
  • Then in the terminal: sqlite3 ex1.db <ex1.sql to create ex1.db
  • Put in terminal: sqlite3 ex1.db.schema
  • Or put: sqlite3 ex1.db [... and then ....]. Schema
+6
source 
 sqlite3 ex1.db < ex1.sql

For the above to work, ex1.sql must already exist. < is a character used in shells to redirect input. sqlite3 starts here with a new or existing database (it will create the database as needed) and receives SQL queries from ex1.sql , executing them and changing ex1.db .

Now let's generate ex1.sql from ex1.db , which you apparently want to do:

 sqlite3 ex1.db .dump > ex1.sql

We again use the shell redirector, now we redirect the output to ex1.sql . The .dump command forces sqlite write SQL statements that will recreate a similar database when they are executed in an empty database: tables are recreated and populated using INSERT , etc.

Now you can go to step 1:

 sqlite3 ex1copy.db < ex1.sql
+10
source

Today I faced the same problem, and, like you, I was wondering why my team is not working. As mentioned above, the author assumes that you create the original command in a text editor and save the .sql file. And then ex1.db <The ex1.sql command converts the file to a .db file.

+2
source

More articles:


All Articles