用Python 来连接postgres (2) - string compostion

We can use string interpolation to compose a SQL query using python strings. Two methods for doing so include:

• Using %s, passing in a tuple as the 2nd argument in cursor.execute()
• Using named string parameters %(foo)s, passing in a dictionary instead

import psycopg2

# Establish the connection to our database
connection = psycopg2.connect('dbname=example')

# Working within session where we can start committing these transactions
# Queue up work into our transaction, we first have to start interacting with a cursor
# Cursor is basically an interface that allows you to start queuing up work and transactions. 
cursor = connection.cursor()

# cursor.execute immediately start executing work on the database.
# Create a transaction

cursor.execute('DROP TABLE IF EXISTS table2;')

cursor.execute('''
    CREATE TABLE table2 (
        id INTEGER PRIMARY KEY,
        completed BOOLEAN NOT NULL DEFAULT False
    );
''') 

# Use %s which turns this string into a template that we can then inject data in, 
cursor.execute('INSERT INTO table2 (id, completed) VALUES (%s, %s);', (1, True))


SQL = 'INSERT INTO table2 (id, completed) VALUES (%(id)s, %(completed)s);'
data = {
    'id': 2,
    'completed': False
}
cursor.execute(SQL, data)

# cursor.execute('INSERT INTO table2 (id, completed)' + ' VALUES (%(id)s, %(completed)s);',{
#     'id': 2,
#     'completed': False
# })

# Commit the work to the database
# connection.commite() will wound up committing both the create table and the insert into altogether. 
connection.commit()

# Clost the connection
# Psychopg2 will not automatically close out your connection for you. 
connection.close()
cursor.close()

通过在Terminal里就能看到，能创建一个table2