Sqlite3 .schema issue

Hello and thank you for your help. I have just gone off the codecademy platform in the SQL learning portion (section 4) and have just opened sqlite3 on my Mac. My terminal is recognizing the imported database but none of the data is showing up when I type .schema so I am unable to enter queries. I have included a picture of what is happening. Thank you so much!
The exact video I am interacting with: https://www.youtube.com/watch?v=4MJSZi4qvIE&feature=youtu.be

How are you measuring that?
Because my first guess is that is an empty or non-existant file

Could the issue be that the file won’t open in numbers on the Mac? Therefore it is empty?

I doubt mac has anything whatsoever to do with it.

You have a file. You tell sqlite about it. Sqlite does stuff.
If there’s no table then, again, my guess is, it’s empty. A 0 byte file.

And whatever this “numbers” is, it probably has no business looking at the file, does it?

Presumably you typed in a path to a file that did not exist, sqlite created it since it wasn’t there, and obviously, there’s nothing in it.

Usually you don’t want to manually type in file names, tab completion both saves you a bunch of typing and confirms that there’s really a file there or it wouldn’t complete. Commands like ls, pwd and cd can be used to find out what files exist in a directory, where you currently are, and to change directory.

Thank you again I appreciate it, the numbers is Mac equivalent to excel, and when I open the file in google spreadsheet it is populated with Data so perhaps I need to download it in google spreadsheet.
But what you’re saying makes sense maybe I need to find a new dataset that actually exists, this was a download from Stanford sqlite databases that I found through Codecademy

but is that the same file?
what I’m suggesting is that you’ve got two files.
one that you’re viewing in a graphical ui, and another one that’s empty in a different location

if it really is the same file I’d be interested to see it

curl --upload-file /replace/this/with/path/to/your/file https://transfer.sh/blah

You’re definitely right, I went ahead and downloaded the google spreadsheet file instead of using the standard download link they provide. See photo below. Then I will try to produce the schema.

here is the link to where I am getting my data : http://2016.padjo.org/tutorials/sqlite-data-starterpacks/#toc-american-community-survey-1-year-data-for-2015

Well that file is totally fine:


There must something I need to download onto the Mac that will allow sqlite3 to read the database, because you’re right at this point when I download it onto the Mac it shows up empty.


You’re just not using the file you downloaded.
If you use some gibberish as path you’ll get the same outcome, because that too does not exist.
There’s no problem opening the file. You’re telling it to open a different file.

Try it with the full path instead, something like ~/Downloads/name/of/the/file

Or do what I did, and download it to the directory that your terminal is currently in.

Okay let me follow what you sent me in your quick demo, thanks

I downloaded it using wget, not sure if you have that. You might.
Otherwise curl is probably more commonplace, basically installed on anything including your literal toaster

curl --remote-name http://2016.padjo.org/files/data/starterpack/census-acs-1year/acs-1-year-2015.sqlite

… but then again maybe you don’t have that either.

in which case, either use cd to navigate to the directory where the real file is, or use the full path to the file

1 Like

I ran the command using curl and it is not returning any column or table information, so I will need to use the full path to access the file as I tried cd but that does not seem to work either. Is there anyway you could provide a quick video demo on how to use the full path in order to access the database in sqlite?

from your screenshot which shows having successfully downloaded the database, you’d only need to do

sqlite3 acs-1-year-2015.sqlite

… and .schema

using full path (as opposed to relative to your current location) isn’t particularly different

sqlite3 /path/to/wherever/the/file/is

since you know where the file is in terms of your graphical interface, that’s how you’d obtain the path, presumably you could right-click (kidding-but-not-really) the file and there’d be an option to show information about it

or for that matter you could copy/move the file to where your terminal is, but, you already did copy it from that web server

you wouldn’t see anything after just using curl … curl doesn’t read databases. it’s a program for sending and receiving data. after running it you’d have the file, nothing’s supposed to happen. but now that you have the file, you can run a different program, sqlite3, and you’d tell sqlite3 about the file you just downloaded

1 Like

Bless you, thank you so much!!! It worked, the initial command
sqlite3 acs-1-year-2015.sqlite

was very successful, I really appreciate your help and bearing with me while I use my training wheels :slight_smile:

moral of the story, then:

don’t type file names without checking whether they’re there
and, the easiest way to verify that is to use tab completion every time you type in a file

sqlite3 ac<press TAB .. possibly twice>

when you use a graphical file explorer you’d only click things you see … using a terminal isn’t any different

a terminal is an excellent file explorer! especially after setting up some key bindings and maybe some custom commands, for example the first thing you’d do after changing directory is almost always to list the files, so you could make a cd that runs both cd and ls

I’m sure I have overcomplicated it a bit, thinking about sqlite as a graphical interface is really helpful!