postgresql practice

1. Environment setup

Reference: http://www.lvesu.com/blog/main/cms-532.html

1.1. Install dependencies

# Need to install postgresql-devel plug-in
yum install postgresql-devel*
# Install pg and py drivers:
# Debian system:
apt-get install libpq-dev python-dev
# RedHat series:
yum install libpqxx-devel python-devel

# Installation is complete, then use
pip install psycopg2

1.2. Install postgres (including psql)

sudo apt-get -y install postgresql
# Start service
systemctl start postgresql.service
systemctl restart postgresql

1.3. Login

1.3.1. Modify the password of postgres user in Linux system

PostgreSQL will create a default linux user postgres. The method to change the user’s password is as follows:

  1. First delete the historical password of user postgres
sudo passwd -d postgres
  1. Reset password for user postgres
sudo -u postgres passwd

Follow the system prompts and enter the new password twice, and you’re done.

1.3.2. Change the password of user postgres

The PostgreSQL database creates an administrator account by default: postgres; changing its password requires only three steps:

  1. First, log in to PostgreSQL
sudo -u postgres psql postgres -p 5432
  1. Then, change the password of the account postgres
ALTER USER postgres WITH PASSWORD 'Lpf65BsDhDNdaJmH';
  1. Finally, exit the pgsql client

2. Database operations

2.1. Create database

# Login
psql -U postgres
#Execute sql
CREATE DATABASE manage;
# Inquire
postgres=# \l;
# Select database
postgres=# \c manage;
# Delete database
DROP DATABASE [IF EXISTS] name
DROP DATABASE IF EXISTS mydb;

2.2. Create table

postgres=# CREATE TABLE questions(
question_id varchar(32) PRIMARY KEY NOT NULL,
question_text varchar(128) NOT NULL,
category varchar(32) NOT NULL,
task_id varchar(32) NOT NULL
);

postgres=# CREATE TABLE answers(
answer_id varchar(32) PRIMARY KEY NOT NULL,
answer_text varchar(128) NOT NULL,
question_id varchar(32) NOT NULL,
model_id varchar(32) NOT NULL,
task_id varchar(32) NOT NULL,
score INT
);
# View all tables
postgres=# \dt
# View the specified table and display table information
postgres=# \d answers

# Delete table
DROP TABLE [IF EXISTS] name
DROP TABLE IF EXISTS answers;

2.3. Multi-table query

https://blog.csdn.net/weixin_67588007/article/details/124832788

2.3.1. Multi-table query through SELECT clause

Syntax:
select field name
from table 1, table 2…
where table1.field = table2.field
and other query conditions
Example: Take the student table student and class table class as an example
Select student.sid, student.sname, student.classid, class.classid, class.classname
from student, class
where student.classid = class.classid
Note: In the above code, the same field information of the two tables is used as a condition to perform a joint query between the two tables. However, this is not recommended in actual development. It is best to use primary and foreign key constraints.

2.3.2. Query through inner join

Syntax:
select field name
from Table 1
inner join table 2
on Table1.Field = Table2.Field
Example: Take the student table student and class table class as an example
innerjoin
select student.sid, student.sname, student.classid, class.classid, class.classname
from student
inner join class
on student.classid = class.classid
innerjoinselect
In this scenario, what is obtained is the internal data of student and class that meet the judgment conditions. Because what is obtained is internal shared data, the connection method is called inner connection.

2.3.3. Query through outer join left join, left outer join, right join, right outer join, union

2.3.3.1. left join

Syntax:
select field name
from Table 1
left join table 2
on Table1.Field = Table2.Field
Example: Take the student table student and class table class as an example
leftjoin
select student.*, class.*
from student
left join class
on student.classid = class.classid
The results are as follows, records that do not exist in the class table are filled with Null:
leftjoinselect
What is obtained in this scenario is all the data of student and the data of classes that meet the same conditions;

2.3.3.2. left outer join

Equivalent to left join + [where Table 2. Field is null]
Syntax:
select field name
from Table 1
left join table 2
on Table1.Field = Table2.Field
where table 2.field is null
Example: Take the student table student and class table class as an example
leftouterjoin
select student.sid, student.sname, class.classid, class.classname
from student
left join class
on student.classid = class.classid
where class.classid is null
leftouterjoinselect
What is obtained in this scenario is all the data in student minus “data that meets the same conditions as class”, and then the remaining data of the student is obtained

2.3.3.3. right join

Syntax:
select field name
from Table 1
right join table 2
on Table1.Field = Table2.Field
Example: Take the student table student and class table class as an example
rightjoin
select student.*, class.*
from student
right join class
on student.classid = class.classid
rightjoinselect
In this scenario, what is obtained is all the data of class and the data of student that meet the same conditions;

2.3.3.4. right outer join

Equivalent to right join + [where Table 1. Field is null]
grammar:
select field name
from Table 1
right join table 2
on Table1.Field = Table2.Field
where table1.field is null
Example: Take the student table student and class table class as an example
rightouterjoin
select student.sid, student.sname, class.classid, class.classname
from student
right join class
on student.classid = class.classid
where student.classid is null
rightouterjoinselect
What is obtained in this scenario is all the data in class minus “data that meets the same conditions as student”, and then the remaining data of class is obtained;

2.3.3.5. left join union right join

grammar:
select field name
from Table 1
left join table 2
on Table1.Field = Table2.Field
union
select field name
from Table 1
right join table 2
?on Table 1.Fields = Table 2.Fields
Example: Take the student table student and class table class as an example
union
select student.*, class.*
from student
left join class
on student.classid = class.classid
union
select student.*, class.*
from student
right join class
on student.classid = class.classid
unionselect
What is obtained in this scenario are public records that meet certain conditions and unique records.

3. psycopg2 connects to the database

3.1. Connect to database

Use the psycopg2 module to connect to a PostgreSQL database. All queries can be executed using the following connection methods. Now I want to specify a different mode than public to execute my SQL statements. Is there a way to specify the schema name in the connection method?

conn = psycopg2.connect(host="localhost",
                            port="5432",
                            user="postgres",
                            password="password",
                            database="database",
                            )

I tried specifying the schema directly inside the method. schema=”schema2″ but I get the following programming error.
ProgrammingError: invalid dsn: invalid connection option “schema”

3.2. Batch insertion

'''Batch insert sql statements'''
import pymysql,string,random,time
def connet_mysql():
    try:
        db=pymysql.connect(host='192.168.31.103',user='root',password='123456',
                           db='test',port=3306)
    except Exception as e:
        print('Database connection failed',e)
    return db

def insert_data(id,username,password):
    db=connet_mysql()
    cursor=db.cursor()
    **sql_1='insert into user_test(id,user,password) values (%s,%s,%s)'**
    sql_2='select * from user_test'
    # Insert using tuple
    **params=(id,username,password)**
    **cursor.execute(sql_1,params)**
    cursor.execute(sql_2)
    db.commit()
    all=cursor.fetchall()#Get the query content through the cursor
    print(all)

def info():
    str_1d=string.digits
    str_2a=string.ascii_letters
    str_3=str_1d + str_2a
    for i in range(501,601):
        id=i
        username='user' + str(i)
        password=''.join(random.sample(str_3,6))
        insert_data(id,username,password)
if __name__ == '__main__':
    info()

4. Database connection pool

Install:
https://blog.csdn.net/weixin_44041700/article/details/110454901

use:
https://blog.51cto.com/u_16213421/7115953
https://www.cnblogs.com/xy-ouyang/p/12987676.html