Data insertion with Date and TimeStamp format into PostgreSQL

Question

I have several weather-related fields such as date_of_data_collection, time, temperature and relative humidity. I have to insert the values of these fields into PostgreSQL. Also, I want to use date and timestamp format for storing date_of_data_collection and time. The table has already been created. However, insertion is the main concern. How can my task be accomplished in Python?

Answer 1

There are 2 ways to connect to a database from python:

1. An ORM like SQLAlchemy
2. A database driver/adapter like psycopg2

These are two completely different things:

• SQLAlchemy generates SQL statements, and
• psycopg2 directly sends SQL statements to the database.

Note: SQLAlchemy depends on psycopg2 or other database drivers to communicate with the database.

Using an ORM will boost your development speed, and you can generate SQL statements by writing in a pythonic way. For example,

from sqlalchemy import create_engine
dbengine = create_engine('postgresql://user@localhost:5432/db')

# Lets create a table with 2 columns
users = Table('users', metadata,
          Column('name', String(40), primary_key=True)
          Column('age', Integer)
        )
users.create()

# And insert some data
u = users.insert()
u.execute (
          {'name': 'John', 'age': 24},
          {'date': 'Doe', 'age': 42},
)

Tip: I prefer using an ORM and SQLAlchemy is excellent library for that.

(Also, it saves you from writing sql)

Using a database driver- psycopg2 you can read more about it here

import psycopg2
DATABASE = psycopg2.connect("dbname='database_name' user='dbuser' host='localhost' password='dbpass'")

# Next step is to define a cursor to work with.
db = DATABASE.cursor()
# Now we can write our SQL statements
db.execute("""SELECT * from table_name""")

# create a table
db.execute('''CREATE TABLE users
          (NAME    TEXT    NOT NULL,
           AGE     INT     NOT NULL,
          );'''
)

# And insert some data
db.execute("INSERT INTO users (NAME,AGE) VALUES ('John', 24)");
db.execute("INSERT INTO users (NAME,AGE) VALUES ('Doe', 42)");

# close communication with database
db.commit()
db.close()