Structured data organizes information into rows and columns: each row represents a record, while each column represents a field. Delimiters such as commas, tabs, or pipes commonly separate fields in text files. section 21.0
Work with structured data by:
- Reading text, CSV, or spreadsheet files, including datasets distributed across many files. section 21.0
- Cleaning, normalizing, sorting, and writing records when fields are incomplete or dirty. section 21.0
- Storing records in relational database tables and retrieving them with SQL or object-based interfaces. section 23.2 section 23.4.1
A small people table
A table with id, name, and count fields can hold rows such as (1, 'Bob', 1), (2, 'Jill', 15), and (3, 'Joe', 10). A query can select all rows, filter by name, and update Jill's count to 20. section 23.2
cursor.execute("create table people (id integer primary key, name text,
count integer)")
cursor.execute("insert into people (name, count) values ('Bob', 1)")
cursor.execute("insert into people (name, count) values (?, ?)",
("Jill", 15))
conn.commit()
Create a SQLite table, insert records with positional parameters, and commit the transaction. section 23.2
dbPath = 'datafile2.db'
engine = create_engine('sqlite:///%s' % dbPath) #1
metadata = MetaData()
people = Table('people', metadata, #2
Column('id', Integer, primary_key=True),
Column('name', String),
Column('count', Integer),
)
Session = sessionmaker(bind=engine) #3
session = Session()
metadata.create_all(engine) #4
Represent the same table through SQLAlchemy metadata, typed columns, an engine, and a session. section 23.4.1
