DB/PostgreSQL (16) 썸네일형 리스트형 [Theorem] Differences between conn.commit and conn.autocommit In PostgreSQL, all user activity occurs in a transaction. If autocommit mode is enabled, each SQL statement forms a single transcation on its on. In this case, no rollback is possible. Source from : https://stackoverflow.com/questions/51880309/what-does-autocommit-mean-in-postgresql-and-psycopg2 https://stackoverflow.com/questions/57761557/is-there-any-reason-not-to-use-autocommit https://stacko.. [Theorem] Difference between VACUUM and VACUUM FULL The vacuum of the PostgreSQL system is a routine maintenance process. The differences between VACUUM and VACUUM FULL is as follows : VACUUM VACUUM FULL 1. It only delets the dead tuples in the table, and there is no real physical deletion; 2. During the vacuum process, the data table can be accessed normaly. 1. Physically delete dead tuples in the table to free up space to the operating system; .. [psycopg2] Vacuuming Postgres Database 1. Speed problem after deleting all rows in table In the case of UPDATE and DELETE, one of the representative desctrutive queries, if we upload it back to the table after executing the cluases, we can see that the contents of the data have not chagned, but the upload speed has been slower than before. import psycopg2 conn = psycopg2.connect(dbname='db', user='ur', password='pw') ccursor = conn.c.. [psycopg2] More efficient Index Scan 1. Bitmap Index/Heap Scan Index Scan has the advantage of showing much faster efficiency that Seq Scan. In SQL, there are cases where a WHERE cluase uses a compound conditional statement rather than a single conditional statement. At this time, if one column is designated as INDEX and the other column is not designated as INDEX, it can be seen that the nested structure of Bitmap Index Scan and B.. [psycopg2] Efficient query with Index Scan 1. What is Index Scan? import psycopg2 import json conn = psycopg2.connect(dbname="db", user="ur", password="pw") cursor = conn.cursor() cursor.execute("EXPLAIN (ANALYZE, FORMAT josn) SELECT * FROM table WHRE id = nn;") query_plan = cursor.fetchone() print(json.dumps(query_plan, indent=2)) # Result of query_plan in json [ [ { "Plan": { "Node Type": "Index Scan", "Parallel Aware": false, "Scan Di.. [psycopg2] Debugging Postgres Queries 1. Exection of queries The steps to perform an SQL query statement are as follows: Query parsing : The query statement is divided into correct grammar units and transformed into query trees if there are no errors. Query rewrite : If there is a special rule identified in the system catalog, reapply it to the query tree. Query planning : Planner and Optimizer optimize query tree and send it to exe.. [psycopg2] Exploring Postgres Database Internals 1. Exploring Internal tables 1.1 Check whole table in database Inside Postgres engine, there is a set of internal tables used to manage the overall structue of the database. These tables are located inside the information_schema or system catalogs table set. The table includes information on data, the name of the table, and the type stored in the database. For example, cursor.description is an a.. [psycopg2] Database Management 1. Database Management 1.1 Create Database Postgres can create multiple databases inside the server. The databse can be created with CREATE DATABASE clause, and the OWNER option is used to specify who owns the databse. If the OWNER option is not specified, the current user user is the owner. import psycopg2 conn = psycopg2.connect(dbname="db", user="ur") conn.autocommit = True cursor = conn.curs.. [psycopg2] User Management 1. Connection to Postgres Server Unlike SQLite3, Postgres is a client-server model, so different users access databases with different priveleges. For the security of the user, Postgres can access the database server by setting the password. A superuser can adjust the permissions of other users while having full permissions on the Postgres server, similar to the sudo permissions of the file syst.. [psycopg2] Loading and Extracting Data with Tables 1. Ways of Loading Data with Tables 1.1 Using mogrify method In psycopg2, external data can be imported into cursor.execute through the %s placeholders. When the placeholder is used in the cursor.execute() method, the value in Python form is automatically changed into Postgres type. This change occurs through the cursor.mogrify() method. The object converted through cursor.mogrify is a bytes obj.. [psycopg2] Checking encoding type of table The encoding are support in PostgreSQL allows us to store text in a variety of encodings, including single-bytes encoding and multiple-byte encoding. All supported encoding can be used transparently by clients and server. We can find encoding of table in PostgresSQL by inspecting the connection.encoding parameters. To change encoding, there is a method called connection.set_client_encoding(). im.. [psycopg2] SQL Injection and Ways to defened it 1. What is SQL Injection? SQL Injection is a method for code injection attack that intentionally exploits application security loopholes to cause malicious SQL statements to be executed, resulting in abnormal manipulation of the database. query = "SELECT * FROM users WHERE username = '" + name + "';" The above code selects a row that receives an ID and password from a user and matches it. Howeve.. [psycopg2] Optimizing Data Types of Tables 1. Check data type of table Inadequate data type schema cause errors when we write queries. The cursor.description attribute of the pyscopg2 package outputs the data type of the database table. The cursor.description property consists of a column object consisting of name and type_code. The name has the name of the column, and the type_code has an integer value corresponding to the data type. im.. [psycopg2] SQL Transactions 1. What is SQL Transactions? A Database Transaction is a unit of interaction in a database management system or a similar system. Here, a similar system means a system in which transactions are clear, mutually independent, consistent, and reliable in success and failure. That is, it refers to a unit of work performed to change the state of the database. In theory, the database system guarentees .. [psycopg2] Executing Postgres query in Python script 1. Workflow of executing query in Python script psycopg2 is a package for using Postgres in Python script. In the same way as sqlite3, a conn instance and a cursor instance are created to create a query. In Postgres, we have transaction notation when we commit the queries to sever. A new transcation will automatically be created when we open a connection in psycopg2. When a commit is called, the.. [psycopg2] How to use Postgres in Python 1. What is Postgres? Relational Database Management System(RDBMS) is a set of software tools that allow multiple users to access data within a database. DBMS allows users or other programs to process and responds appropriately to use data. For SQLite, only a single process is allowed to write to the database (It only process query statement one by one.). Therefore, there is a limit to working wi.. 이전 1 다음
