<jupyter_start><jupyter_text>Start PostgreSQL server & Create databaseDo this first``` init Postgresinitdb postgres start the postgres serverpg_ctl -D postgres start create your databasecreatedb testdb```Then start the jupyter-notebook  Connecting Python to PostgreSQL[PostgreSQL - Python Interface](https://www.tutorialspoint.com/postgresql/postgresql_python.htm)<jupyter_code>import psycopg2
conn = psycopg2.connect(dbname='testdb')
# creates a cursor which will be used throughout of your database programming with Python
cur = conn.cursor()<jupyter_output><empty_output><jupyter_text>Creating a table[PostgreSQL Python: Create Tables](http://www.postgresqltutorial.com/postgresql-python/create-tables/) to create new tables in the PostgreSQL database using Python.<jupyter_code>my_table = "newtable"

command = """
        CREATE TABLE {table} (
            color_id SERIAL PRIMARY KEY,
            color_name VARCHAR(255) NOT NULL
        )""".format(table=my_table)
cur.execute(command)<jupyter_output><empty_output><jupyter_text>Inserting data into table**One row**`cursor.execute(sql [, optional parameters])`This routine executes an SQL statement. The SQL statement may be parameterized (i.e., placeholders instead of SQL literals). The psycopg2 module supports placeholder using %s sign. For example:`cursor.execute("insert into people values (%s, %s)", (who, age))`<jupyter_code>sql = """
    INSERT INTO {table}(color_name)
    VALUES(%s) RETURNING color_id;""".format(table=my_table)
# execute the INSERT statement
cur.execute(sql, ("red",))<jupyter_output><empty_output><jupyter_text>**Many rows**    `curosr.executemany(sql, seq_of_parameters)`This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql.<jupyter_code>vendor_names = [
    ('orange',),
    ('yellow',),
    ('green',),
    ('blue',),
    ('indigo',),
    ('violet',)
]
cur.executemany(sql, vendor_names)<jupyter_output><empty_output><jupyter_text>Selecting data`cursor.fetchall()`This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available.<jupyter_code>cur.execute("SELECT * from {table}".format(table=my_table))
rows = cur.fetchall()
for row in rows:
    print(row)<jupyter_output>(1, 'red')
(2, 'orange')
(3, 'yellow')
(4, 'green')
(5, 'blue')
(6, 'indigo')
(7, 'violet')<jupyter_text>Commit changes`connection.commit()`This method commits the current transaction. If you don't call this method, anything you did since the last call to commit() is not visible from other database connections.<jupyter_code># close communication with the PostgreSQL database server
cur.close()

# commit the changes
conn.commit()

# close connection
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11847317248829672, 'fraction_numerical': 0.004681310767014764, 'mean_word_length': 5.997481108312343, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 1, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '129089', 'license': 'MIT', 'n_tokens_mistral': 878, 'n_tokens_neox': 846, 'n_words': 246, 'path': 'src/ipynb/day42_python_and_postgresql.ipynb', 'repo_name': 'csiu/kick', 'size': '2777'}