If you are using a mac - open a terminal window and type
sqlite3you will start sqlite3 on the command line. We will progress toward using sqlite3 from within the ipython notebook but now it might be easier to understand in this simple format.
There are two types of commands I'm going to show you. Some start with a
.like
.helpthis is how you can give instructions to the database engine, the rest of the commands are in the SQL language and that is how we create and query the information in our database.
Right now, if you are at the sqlite prompt, you can prettify the output by entering the following
.headers on
.mode columnsThis will cause the output to include headers and align the output in columns. Much nicer and easier to read.
The SQL CREATE TABLE Statement
The CREATE TABLE statement is used to create a table in a database.
Tables are organized into rows and columns; and each table must have a name.
SQL CREATE TABLE Syntax
CREATE TABLE table_nameHow to write output of an SQL query to a csv
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
sqlite> .mode csv
sqlite> .separator ,
sqlite> .output test_file_1.csv
sqlite> select * from tbl2;
sqlite> .exit
Psycholinguistic_databases Wintermute$ cat test_file_1.csv
id,word
0,aaron
1,aargh
Psycholinguistic_databases Wintermute$
import sqlite3 conn = sqlite3.connect("mydatabase.db") # or use :memory: to put it in RAM cursor = conn.cursor() # create a table cursor.execute("""CREATE TABLE albums (title text, artist text, release_date text, publisher text, media_type text) """)
# insert some data cursor.execute("INSERT INTO albums VALUES ('Glow', 'Andy Hunter', '7/24/2012', 'Xplore Records', 'MP3')") # save data to database conn.commit() # insert multiple records using the more secure "?" method albums = [('Exodus', 'Andy Hunter', '7/9/2002', 'Sparrow Records', 'CD'), ('Until We Have Faces', 'Red', '2/1/2011', 'Essential Records', 'CD'), ('The End is Where We Begin', 'Thousand Foot Krutch', '4/17/2012', 'TFKmusic', 'CD'), ('The Good Life', 'Trip Lee', '4/10/2012', 'Reach Records', 'CD')]cursor.executemany("INSERT INTO albums VALUES (?,?,?,?,?)", albums)conn.commit()
import sqlite3 conn = sqlite3.connect("mydatabase.db") cursor = conn.cursor() sql = """ UPDATE albums SET artist = 'John Doe' WHERE artist = 'Andy Hunter' """ cursor.execute(sql) conn.commit()
import sqlite3 conn = sqlite3.connect("mydatabase.db") cursor = conn.cursor() sql = """ DELETE FROM albums WHERE artist = 'John Doe' """ cursor.execute(sql) conn.commit()
import sqlite3 conn = sqlite3.connect("mydatabase.db") #conn.row_factory = sqlite3.Row cursor = conn.cursor() sql = "SELECT * FROM albums WHERE artist=?" cursor.execute(sql, [("Red")]) print cursor.fetchall() # or use fetchone() print "\nHere's a listing of all the records in the table:\n" for row in cursor.execute("SELECT rowid, * FROM albums ORDER BY artist"): print row print "\nResults from a LIKE query:\n" sql = """ SELECT * FROM albums WHERE title LIKE 'The%'""" cursor.execute(sql) print cursor.fetchall()
How do i import a xls file data into sqlite3
The table must exist first. i can create the blp-items table withCREATE TABLE blp_items (spelling TEXT, lexicality TEXT(1), rt REAL, zscore REAL, accuracy REAL, rt_sd REAL, zscore_sd REAL, accuracy_sd REAL );use the .import commandthe .import function will import the headers from the csv file.separator "," .import blp-items.csv blp_items
SELECT * FROM blp_items LIMIT 10;spelling lexicality rt zscore accuracy rt_sd zscore_sd accuracy_sd---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------spelling lexicality rt zscore accuracy rt.sd zscore.sd accuracy.sda/c N 668.036363 0.32196203 0.76923076 180.088554 1.09726646 0.424052095aband N 706.5 0.48159747 0.925 170.172149 0.86149330 0.266746782abayed N 689.935483 0.15776501 0.86842105 281.050165 1.02394376 0.342569987abbear N 560.583333 -0.3614142 0.95 121.360472 0.52986732 0.220721427abbears N 611.410256 -0.1372765 0.975 169.857946 0.89889305 0.158113883abbens N 598.289473 -0.3845125 1.0 144.779964 0.59991825 0.0abchaim N 591.763157 -0.1323686 1.0 128.560940 0.65425409 0.0abects N 635.205128 -0.0324051 0.975 155.666002 0.64429156 0.158113883abeme N 591.055555 -0.2430494 0.94736842 182.908243 0.93264000 0.226294285we can delete this line byDELETE FROM blp_items WHERE lexicality='lexicality';spelling lexicality rt zscore accuracy rt_sd zscore_sd accuracy_sd---------- ---------- -------------- ------------ ------------ -------------- ------------ ------------a/c N 668.0363636364 0.3219620388 0.7692307692 180.0885547226 1.0972664605 0.4240520956aband N 706.5 0.4815974773 0.925 170.1721499131 0.8614933071 0.2667467828abayed N 689.935483871 0.1577650108 0.8684210526 281.0501658048 1.0239437649 0.3425699875abbear N 560.5833333333 -0.361414284 0.95 121.3604725012 0.5298673287 0.2207214279abbears N 611.4102564103 -0.137276547 0.975 169.8579465239 0.8988930562 0.158113883abbens N 598.2894736842 -0.384512528 1.0 144.7799649972 0.5999182553 0.0abchaim N 591.7631578947 -0.132368665 1.0 128.5609402685 0.6542540915 0.0abects N 635.2051282051 -0.032405186 0.975 155.6660020156 0.6442915682 0.158113883abeme N 591.0555555556 -0.243049439 0.9473684211 182.9082431079 0.9326400097 0.2262942859abents N 690.2564102564 0.4099671341 0.975 207.919356577 0.8366905183 0.1581138and then dump the sql output.output blp_items.sql.dump blp_items
No comments:
Post a Comment