<jupyter_start><jupyter_text>Notes from Using PostgreSQL with Python<jupyter_code># conda install -c anaconda psycopg2
import psycopg2
conn = psycopg2.connect("dbname=dq user=dq")
cur = conn.cursor()
print(cur)
conn.close()<jupyter_output><empty_output><jupyter_text>Transactions ensure that all the queries in a transaction block are executed at the same time. If any of the transactions fail, the whole group fails, and no changes are made to the database at all.Whenever we open a Connection in psycopg2, a new transaction will automatically be created. All queries run up until the commit method is called will be placed into the same transaction block. When commit is called, the PostgreSQL engine will run all the queries at once.If we don't want to apply the changes in the transaction block, we can call the rollback method to remove the transaction. Not calling either commit or rollback will cause the transaction to stay in a pending state, and will result in the changes not being applied to the database.<jupyter_code>conn = psycopg2.connect("dbname=dq user=dq")
cur = conn.cursor()
cur.execute("CREATE TABLE notes(id integer PRIMARY KEY, body text, title text)")
conn.close()<jupyter_output><empty_output><jupyter_text>There are cases when you won't want to manage a transaction, and you'll instead want changes right away. This is most common when you're making changes to the database that you want to be guaranteed to happen immediately.Some changes also have such widespread effects that they can't be wrapped inside of a transaction. One example of this is creating a database. When creating a database, we'll need to activate autocommit mode first.<jupyter_code>conn = psycopg2.connect("dbname=dq user=dq")
conn.autocommit = True
cur = conn.cursor()
cur.execute("CREATE TABLE facts(id integer PRIMARY KEY, country text, value text)")
conn.close()<jupyter_output><empty_output><jupyter_text>- Connect to the dq database as the user dq.   - Execute a SQL query that inserts a row into the notes table with the following values:   - id -- 1   - body -- 'Do more missions on Dataquest.'   - title -- 'Dataquest reminder'.  - Execute a SQL query that selects all of the rows from the notes table.  - Fetch all of the results and print them out.  - Commit your changes and close the Connection.<jupyter_code>conn = psycopg2.connect("dbname=dq user=dq")
cur = conn.cursor()
cur.execute("INSERT INTO notes VALUES (1, 'Do more missions on Dataquest.', 'Dataquest reminder');")
conn.commit()
cur.execute("SELECT * from notes;")
rows = cur.fetchall()
print(rows)
conn.close()<jupyter_output><empty_output><jupyter_text>Using Owners<jupyter_code>conn = psycopg2.connect("dbname=dq user=dq")
conn.autocommit = True
cur = conn.cursor()
cur.execute("CREATE DATABASE income OWNER dq;")
conn.close()
conn = psycopg2.connect("dbname=dq user=dq")
conn.autocommit = True
cur = conn.cursor()
cur.execute("DROP DATABASE income;")
conn.close()<jupyter_output><empty_output><jupyter_text>Command line<jupyter_code>psql -d bank_accounts <<EOF
CREATE TABLE deposits (
    id integer PRIMARY KEY,
    name text,
    amount float
);
EOF<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08549715009499684, 'fraction_numerical': 0.0034832172260924636, 'mean_word_length': 5.343373493975903, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '303936', 'license': 'Apache-2.0', 'n_tokens_mistral': 942, 'n_tokens_neox': 908, 'n_words': 382, 'path': '4.3 PostgreSQL Databases/Using PostgreSQL Databases.ipynb', 'repo_name': 'orspain/Dataquest', 'size': '3158'}