Skip to content

Instantly share code, notes, and snippets.

@seven0525
Last active May 24, 2018 02:46
Show Gist options
  • Save seven0525/8548f82dc491a23223e4321e14c35f07 to your computer and use it in GitHub Desktop.
Save seven0525/8548f82dc491a23223e4321e14c35f07 to your computer and use it in GitHub Desktop.
「自作Python100本ノック」9日目(53本〜62本目) ref: https://qiita.com/ahpjop/items/e206de89cf30b37476bc
##Q62: bookテーブルのtitle列を選択し、アルファベット順に表示せよ。
    
```q62.py
sql = "select title from book order by title asc"
for row in db.execute(sql):
print(row)
#16進文字列
hex_str = '47494638396101000100800000000000ffffff21f90401000000002c000000000100010000020144003b'
import binascii
gif = binascii.unhexlify(hex_str)
gif[:6] == b'GIF89a'
#Unicode文字列ではなく、バイト列を定義するためにbを使わなければならないことに注意
#バイト列をバイト列を比較することはできるが、バイト列と文字列を比較することはできない。
#text1
test1 = "This is a test of the emergency text system"
outfile = open("test.txt", "wt")
outfile.write(test1)
outfile.close()
#withを使うとclose呼び出しを避けることができる
with open("test.txt", "wt") as outfile:
outfile.write(test1)
with open("test.txt", "rt") as infile:
test2 = infile.read()
test2
##テキスト
text = '''author, book
J R R Tolkien, The Hobbit
Lynne Truss, "Eats, Shoots & Leaves" '''
#保存
with open("books.csv", "wt") as outfile:
outfile.write(text)
#読み込み
import csv
with open("books.csv", "rt") as infile:
books = csv.DictReader(infile)
for book in books:
print(book)
import linecache
theline = linecache.getline("text.txt", 3)
theline
count = len(open("text.txt", "rU").readlines())
count
#データベースの作成
import sqlite3
db = sqlite3.connect("books.db")# connect():データベースへの接続を開設する(ユーザー名、パスワード、サーバーアドレス、その他引数が指定可能)
curs = db.cursor()#クエリーを管理するカーソルオブジェクトを作る
curs.execute('''create table book (title text, author text, year int)''')# データベースに対してSQLコマンドを実行する
db.commit()
#テキスト
text = '''title,author,year
The Weirdstone of Brisingamen, Alan Garner, 1960
Perdido Street Station, ChinaMiéville,2000
Thud!, Terry Pratchett,2005
The Spellman Files, Lisa Lutz,2007
Small Gods, Terry Pratchett, 1992
'''
#csvファイルの作成
with open("many_books.csv", "wt") as outfile:
outfile.write(text)
#読み取り、挿入
import csv
import sqlite3
ins_str = "insert into book values(?, ?, ?)"
with open("many_books.csv", "rt") as infile:
books = csv.DictReader(infile)
for book in books:
curs.execute(ins_str, (book["title"], book["author"], book["year"]))
db.commit()
sql = "select title from book order by title asc"
for row in db.execute(sql):
print(row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment