Shulou(Shulou.com)05/31 Report--

This article Xiaobian introduces in detail for you "python how to write the data in Dataframe format into the opengauss database and query" with detailed contents, clear steps, and proper handling of the details. I hope that this article "python how to write the data in Dataframe format into the opengauss database and query" can help you solve your doubts.

First, write the data to opengauss

Prerequisite preparation:

Successfully opengauss the database and create the user jack and the database datasets.

Data preparation:

The data used is stored locally in csv format and encoded in GB2312 format.

Data storage:

At first, the hello table does not exist, then after executing the program, the system will automatically create a hello table (here the name is specified as hello)

If the hello table already exists, the data will be added to the hello table. The column name needs to correspond to the hello table one by one.

# load the necessary python library from sqlalchemy import create_engineimport pandas as pd # read data locally df = pd.read_csv ("E:/jiema.csv", low_memory=False,encoding='gb2312') # create database engine # create_engine description: driver://user:password@host:port/dbnameengine = create_engine ('postgresql://jack:gauss@111@192.168.80.130:26000/datasets') # write data try: df.to_sql (' hello' Engine,index=False,if_exists='append') # hello is the name of the database table created except Exception as e: print (e)

Use navicat to view the effect:

II. Python conditional query data of column names in opengauss database

Question:

Due to the requirements of the project, the column names in the database are named in Chinese, which leads to a lot of problems in the later query.

Solution:

The whole SQL statement needs to be wrapped in single quotation marks and the Chinese column name needs to be wrapped in double quotation marks.

Import psycopg2 def dataFromDB (sql): # Connect to the database conn = psycopg2.connect (database='datasets', user='jack', password='gauss@111', host='192.168.80.130', port='26000') curs = conn.cursor () # write Sql Only take the first two rows of data # sql = 'select * from table_name limit 2' # in the database and execute the sql command curs.execute (sql) # to get the data data = curs.fetchall () print (data) # close the pointer and database curs.close () conn.close () sql = 'SELECT "telemetry parameter 2", "telemetry parameter 2 temperature" from source2decode where "engineering parameter. Source address "= 26 'dataFromDB (sql)

Read here, the "python how to write data in Dataframe format into the opengauss database and query" article has been introduced, want to master the knowledge of this article also need to practice and use in order to understand, if you want to know more about the article, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.