Year: 2023

[SQL] Insert method

Agnieszka

However you will try to learn SQL from the beginning, you will find an examples of how to insert data into created tables, like here:

insert into ForestDistricts
 values
(1, 'AUGUSTOW', 12),

And some examples of how to modify existing tables and the values in the columns, using: ‘add, drop, delete and update’ methods for selected rows sometimes with where condition.

Alter table LargeWild
Alter column ...

Update LargeWild
Set ....

But what to do if I want to insert data with one query in freshly added column?
I was searching on the internet for some methods and found not enough. Even asked AI.
But all of the results boiled down to one bassic option: need to set it line by line.
Well, I don’t want to import data from any other file to make it easier, but also tired of typing insert values over and over again.

Tried this. Based on data I had, I convert it to CSV file with comma separator, adding extra column at first and at the end of data, and copy data directly from file to other query.

Select first row and copy into query, where I created SQL table. No executing, go further, to prepare all inserts. There is 17 lines exluding headers. So I tried this: replace all values in the first column from XColF into ‘ INSERT INTO ForestReport VALUES ( ‘ and ‘ XColEnd ‘ into );

Now I want to add new column and data for each row . I know there is autonumration for first column: F_ID. I will use it as a condition of where in last column of external CSV file:

Change word random into ‘update Report_RL set Value4 = ‘ , last word end into ‘ where F_ID = ‘

SQL query below:

Create database Reports

GO

Use Reports

GO 

create table Report_RL(
F_ID int identity(1,1) not null,
City varchar(255) not null,
FCounter int not null,
Hectars numeric (38,4) not null,
Value1 decimal (38,2) not null,
Value2 decimal (38,2) not null,
Value3 decimal (38,2) not null
)
GO

INSERT INTO Report_RL VALUES ('Białystok',59,35602.8,26531.35,91368.29,340478.33);
INSERT INTO Report_RL VALUES ('Gdańsk',82,0,4959.56,56794.76,171705.65);
INSERT INTO Report_RL VALUES ('Katowice',78,3197.08,5902.21,211127.13,122594.17);
INSERT INTO Report_RL VALUES ('Kraków',83,26201.47,3416.29,70011.11,186456.09);
INSERT INTO Report_RL VALUES ('Krosno',52,43011.95,10358.75,188980.01,217156.68);
INSERT INTO Report_RL VALUES ('Lublin',115,12975.55,8091.54,135763.77,84531.36);
INSERT INTO Report_RL VALUES ('Łódź',48,57.42,6964.87,72250.13,108477.83);
INSERT INTO Report_RL VALUES ('Olsztyn',51,0,9544.7,52282.71,317584.22);
INSERT INTO Report_RL VALUES ('Piła',35,0,3513.47,2.28,207518.64);
INSERT INTO Report_RL VALUES ('Poznań',54,4802.51,2331.09,62209.86,179011.76);
INSERT INTO Report_RL VALUES ('Radom',50,7221.75,5781.35,87554.61,241551.04);
INSERT INTO Report_RL VALUES ('Szczecin',74,14277.49,5446.83,67313.01,176636.26);
INSERT INTO Report_RL VALUES ('Szczecinek',72,6188.09,5702.29,38206.56,91233.02);
INSERT INTO Report_RL VALUES ('Toruń',51,3936.05,6639.89,139099.53,196312.35);
INSERT INTO Report_RL VALUES ('Warszawa',47,27514.29,6661.02,36867.79,119665.86);
INSERT INTO Report_RL VALUES ('Wrocław',105,10221.5,4563.94,118368.73,53682.89);
INSERT INTO Report_RL VALUES ('Zielona Góra',54,0,1440.5,18706.23,127667.37);

alter table Report_RL
add Value4 real

GO

update Report_RL set Value4 = 446444.64 where F_ID = 1
update Report_RL set Value4 = 127266.05 where F_ID = 2
update Report_RL set Value4 = 96485.8 where F_ID = 3
update Report_RL set Value4 = 135814.28 where F_ID = 4
update Report_RL set Value4 = 317439.49 where F_ID = 5
update Report_RL set Value4 = 237191.37 where F_ID = 6
update Report_RL set Value4 = 21207.63 where F_ID = 7
update Report_RL set Value4 = 237995.67 where F_ID = 8
update Report_RL set Value4 = 169755.11 where F_ID = 9
update Report_RL set Value4 = 109025.97 where F_ID = 10
update Report_RL set Value4 = 127097.26 where F_ID = 11
update Report_RL set Value4 = 336737.41 where F_ID = 12
update Report_RL set Value4 = 203530.28 where F_ID = 13
update Report_RL set Value4 = 161573.37 where F_ID = 14
update Report_RL set Value4 = 106142.1 where F_ID = 15
update Report_RL set Value4 = 321604.39 where F_ID = 16
update Report_RL set Value4 = 88478.03 where F_ID = 17

GO

select * from Report_RL

Python practices with AI

Agnieszka

How to import and read line by line text file in Python?

I shared some ideas on my website, like the simplest way of Read Files or using libraries: OS, Linecache. When I asked the same question on ChatGPT I received this:

Source: chat.openai.com

AI desribed really deeply all the details:

In this example, we use a with statement, which is a context manager, to open the file. This ensures that the file is automatically closed after the with block, even if an exception occurs. Make sure to replace ‘path/to/your/file.txt’ with the actual path to your text file. The line.strip() method is used to remove the newline character (\n) at the end of each line, which is added by default during reading. You can process each line as needed inside the loop. Remember to handle exceptions (e.g., FileNotFoundError and IOError) that may occur if the file does not exist or if there are issues with reading the file.

Source: chat.openai.com

I needed to operate on data after import and I asked:
how to import only last column from text file in Python?
AI showed me another sample of a code, which I used, but modified a little to suit it my case:

file_path = 'Movies.txt'

years = []

try:
    with open(file_path, 'r') as file:
        for line in file:
            columns = line.strip().split('\t')
            year = columns[-1]
            years.append(year)
except FileNotFoundError:
    print("File not found.")
except IOError:
    print("Error while reading the file.")

Then I tested some operations on it by myself:

print(years)
print(type(years))
tpl = tuple(years[1:10])
print(tpl)
print(max(tpl),min(tpl))
print(len(tpl))
print(len(years))

import statistics

print(statistics.median(tpl))
print(statistics.median(years))

And the final inquiry to AI in this topic: how to export as an Excel file in Python?
Offered me something like this (modified to my example):

import pandas as pd
# Create a DataFrame from the list of ages
data_frame = pd.DataFrame({'ReleaseYear': years})

# Export the DataFrame to an Excel file
data_frame.to_excel('D:\ReleaseYear.xlsx', index=False)

print("Data exported to Excel successfully.")

Must say that chatGPT might be useful as long as you know, what you searching for and have some basic knowledge to understand it and modify it by yourself. And what is important nowadays, also in other categories than programming, stay strong with your own rules and standards to keep distance to all you will get from AI 🙂