I would like to get some understanding on the question that I was pretty sure was clear for me. Is there any way to create table using psycopg2 or any other python Postgres database adapter with the name corresponding to the .csv file and (probably the most important) with columns that are specified in the .csv file.
1 Answer
I'll leave you to look at the psycopg2 library properly - this is off the top of my head (not had to use it for a while, but IIRC the documentation is ample).
The steps are:
- Read column names from CSV file
- Create "CREATE TABLE whatever" ( ... )
Maybe INSERT data
import os.path my_csv_file = '/home/somewhere/file.csv' table_name = os.path.splitext(os.path.split(my_csv_file)[1])[0] cols = next(csv.reader(open(my_csv_file)))
You can go from there...
Create a SQL query (possibly using a templating engine for the fields and then issue the insert if needs be)
4 Comments
I'll definetely try your solution, but let me ask about declaring the type for each column, I'm basically expecting only 2 file types with different columns between those 2 types. Is it possible to store the type for each column in a dictionary and then based on the file type pass that dictionary to the create table statement. I'm pretty sure It's not possible with the dictionary type but could you please to possible implementation. Thanks for a quick response.
Jon Clements
You could have one dictionary of {'file1.csv': 1, 'file2.csv': 2, 'file3.csv': 1} which maps filename to one of your two file formats. Then another one such as { (1, 'name'): 'text', (1, 'gender'): 'char(1)', (2, 'age'): 'integer'}... The dictionary contains a 2-tuple of filetype and column name and maps to the column type. Is that what you're asking?
Jon Clements
They won't accept the dictionary, but you use those to construct a valid SQL query string