Programming in python

Programming in python

background

I'm learning Python, so I'm thinking of making something. Let's make an Insert statement with python.

Target users (example)

I'm making a web service for portfolio creation at home Those who want to use Insert Creation conveniently as a tool for data creation.

Non-target users

People who think that it is 0 if it can not be used in the field. A person who comes to the developer by saying a review or a customer. A person who comes impulsively every day saying only progress. People who only make complaints.

Investigation

Based SQL statement create table drivers ( id serial primary key, first_name varchar, last_name varchar ); INSERT INTO drivers (first_name, last_name) VALUES ('Amy', 'Hua'); Requirements: Enclose the string in single quotes. Even if the item is variable, it can be handled. The table name will be set by specifying the input file name.

draft

-Read data from a file. -Create SQL. -Write to a file.

environment

We suggest using Colaboratory. https://colab.research.google.com/notebooks/welcome.ipynb?hl=ja See the address below for how to use https://obgynai.com/google-colaboratory/

The SQL statement assumes postgresql.

Image

Reference GITHUB

https://github.com/noikedan/INSERTSQL/tree/master/pythonInsrtSql

Below source code

drivers.csv
VARCHAR（n）,VARCHAR（n）
first_name,last_name)
Amy,Haru
Gimmy,Smith

#drivers.Edit csv and change the file name to the table name+Change to csv
input = './sample_data/drivers.csv'
output = './sample_data/output.txt'

table = input.split('/')[-1].split('.')[0]
with open (input, encoding='utf-8') as f:
    with open('output.txt', 'w', encoding='utf-8') as g:
        contents = "Insert into " +  table +"("
        i = 0
        for row in f:
            if i == 0:
               typeList = row.rstrip().split(',')
            if i ==1:
               columList = row.rstrip().split(',')
               k = 0
               for c in columList:
                   if len(columList) == k+1:
                        contents = contents + c + 'VALUES ('
                   else:
                       contents = contents + c + ','
                   k = k + 1
            basecontets = contents

            if i >= 2:
                j = 0
                for r in row.rstrip().split(','):
                    if not 'INTEGER' in typeList[j]:
                        r = "'"+ r +"'"
                    if len(row.rstrip().split(',')) == j+1:
                             basecontets = basecontets +r
                    else:
                            basecontets = basecontets +r+','
                    j = j + 1
                basecontets = basecontets+ ');' + '\n'
                g.write(basecontets)
                basecontets = contents
            i = i + 1
        print("Creation completed")

output.txt
Insert into drivers(first_name,last_name)VALUES ('Amy','Haru');
Insert into drivers(first_name,last_name)VALUES ('Gimmy','Smith');