python+postgreSql

最新推荐文章于 2024-10-05 22:20:15 发布

diaocimo4777

最新推荐文章于 2024-10-05 22:20:15 发布

阅读量211

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：http://www.cnblogs.com/lilyfindjobs/p/4352642.html

本文详细介绍如何使用Python psycopg2库连接PostgreSQL数据库，加载数据到特定表并进行范围分区。通过具体代码示例，展示了从文件加载评分数据到Ratings表的过程，以及如何根据评分值创建和填充多个范围分区。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

def getopenconnection(user='postgres', password='1234', dbname='dds_assgn1'):
    return psycopg2.connect("dbname='" + dbname + "' user='" + user + "' host='localhost' password='" + password + "'")


def loadratings(ratingstablename, ratingsfilepath, openconnection):
    cur = openconnection.cursor()
    cur.execute('Drop Table if exists Ratings; CREATE TABLE Ratings(UserID int NOT NULL, movieid int NOT NULL, Rating double precision, CONSTRAINT key PRIMARY KEY (UserID, movieid));')
    with open(ratingsfilepath, "r") as ins:
        for line in ins:
            array=line.split("::")
            cur.execute('INSERT INTO Ratings VALUES ({0},{1},{2})' .format(array[0],array[1],array[2]))
    openconnection.commit()
    cur.close()
    pass

def rangepartition(ratingstablename, numberofpartitions, openconnection):
    cur=openconnection.cursor()
    #part=(5-0.5)/numberofpartitions
    part=5/numberofpartitions
    for i in range(0,numberofpartitions):
        str='Drop Table if exists range_part{0};CREATE TABLE range_part{0}(UserID int NOT NULL, movieid int NOT NULL, Rating double precision, PRIMARY KEY (UserID, movieid));'.format(i)
        print(str)
        cur.execute(str)
    cur.execute('select * from {0}'.format(ratingstablename))
    result=cur.fetchall()
    for row in result:
        #i=(row[2]-0.01-0.5)/part
        i=(row[2]-0.01)/part
        i=int (i)
        cur.execute('INSERT INTO range_part{3} VALUES ({0},{1},{2})' .format(row[0],row[1],row[2],i))
    cur.execute('Drop Table if exists hrange_record;CREATE TABLE hrange_record(n text NOT NULL,part text NOT NULL);')
    cur.execute('INSERT INTO hrange_record VALUES ({0},{1})' .format(numberofpartitions,part))
    openconnection.commit()
    cur.close()
    pass

1, 构建一个和已有的表格schema一样的表格：

Drop Table if exists part; create table part ( like tablename INCLUDING ALL)
Drop Table if exists part; create table part ( like tablename INCLUDING ALL, like tablename2 including all)

　2, 将选出的结果传入另一个表格中，注意表格的结构和顺序要一致

insert INTO part  SELECT *  FROM tablename where condition

　3，将某一column加上order顺序，并insert另一个表，order也可以不从1开始，只需要＋num 就可以

insert into tablename SELECT *,row_number() OVER (ORDER BY columnname) AS i FROM tablename2

insert into tablename SELECT *,row_number() OVER (ORDER BY columnname)＋2 AS i FROM tablename2

　　4，更改column的名字

ALTER TABLE tablename RENAME COLUMN columnname TO columnname2

转载于:https://www.cnblogs.com/lilyfindjobs/p/4352642.html