Using types to insert data : PostgreSQL « Database « Python Tutorial






import psycopg, time

dsn = 'dbname=dbname user=username' 
print "Connecting to %s" % dsn
dbh = psycopg.connect(dsn)
print "Connection successful."

cur = dbh.cursor()
cur.execute("""CREATE TABLE myTable (
        mydate    DATE,
        mytimestamp TIMESTAMP,
        mytime  TIME,
        mystring varchar(30))""")
query = """INSERT INTO myTable VALUES (
    %(mydate)s, %(mytimestamp)s, %(mytime)s, %(mystring)s)"""
rows = ( \
        {'mydate': psycopg.Date(2009, 12, 25),
         'mytimestamp': psycopg.Timestamp(2009, 12, 15, 06, 30, 00),
         'mytime': psycopg.Time(6, 30, 00),
         'mystring': 'message!'},
        {'mydate': psycopg.DateFromTicks(time.time()),
         'mytime': psycopg.TimeFromTicks(time.time()),
         'mytimestamp': psycopg.TimestampFromTicks(time.time()),
         'mystring': None})
cur.executemany(query, rows)
dbh.commit()
dbh.close()








15.4.PostgreSQL
15.4.1.Query data
15.4.2.Basic connection to PostgreSQL with psycopg
15.4.3.Query PostgreSQL database with select statement
15.4.4.Create a table and insert data
15.4.5.executemany() for batch update
15.4.6.optimized execute() for multiple rows example
15.4.7.Fetch all rows after query
15.4.8.Adjust the connect() call below for your database
15.4.9.Fetch row one by one
15.4.10.Fetch row by using cursor
15.4.11.Parameter style
15.4.12.Get cursor
15.4.13.Using types to insert data
15.4.14.Commit example