-1

I want to import a large csv file without creating table before for that csv file into postgresql. While searching on stackoverflow, one of the user suggested the following code:

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/mydatabase')
df.to_sql('table_name', engine)

Based on this I wrote the following code based on the information for my postgresql which has username = "postgres" and password = "Katherine@412":

from sqlalchemy import create_engine
engine = create_engine('postgresql://[postgres]:Katherine@412@postgres:5432/covid_deaths.csv')
df.to_sql('covid_deaths_owid', engine)

However it is giving me the following error:

OperationalError: (psycopg2.OperationalError) could not translate host name "412@postgres" to address: Unknown server error

Can someone tell me what I am doing wrong?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Sam
  • 65
  • 2
  • 9

2 Answers2

6

The problem is that the password in your connection string contains the 'at' symbol, @. This is interpreted as marking the end of the password and the beginning of the hostname.

The quick solution is to replace the first @ symbol with %40. The connection string is a kind of URI and %40 is the URI encoding of @ (it will be converted into the correct symbol when the password is processed).

In general you can encode any string in a URI-safe manner as follows, working in Python:

import urllib.parse

encoded = urllib.parse.quote_plus(unencoded)

You also have square brackets around your username for some reason, that may also cause problems after you solve the first problem.

Paddy Alton
  • 1,855
  • 1
  • 7
  • 11
  • What I have done to this was. I had imported my csv file into sqlite and created a sql file from their I copy pasted the CREATE TABLE command onto the postgresql editor and then once table was created I used \i "File_path" to input the csv file into the postgresql. i hope this might help someone. – Sam Apr 30 '22 at 15:14
1

localhost ist a valid hostname, like 127.0.0.1 or any other ip adresses.

As long as your maschine or you have an actual server that you can ping with the name postgres.

So determine where your postgres server is running, if the local machine localhostis the correct server name, if ot is another machine, you need to enter theioadrees or the name you can reach it

more about hostgnames you can find in wikipedia

nbk
  • 45,398
  • 8
  • 30
  • 47
  • Thank you for your reply. My main aim is to import a csv file into postgresql without first creating a table for it. Since we usually create a table before importing a csv file in postgresql. My csv file has lots of columns. is there any other convenient way to do this? – Sam Apr 28 '22 at 20:17
  • load the first line only and make a create table from the data you received, and then import it like the link in the other question expalined – nbk Apr 28 '22 at 20:25
  • I did not understand. you mean I should only load: from sqlalchemy import create_engine. Afterwards how do I make a create table???? – Sam Apr 28 '22 at 20:39
  • no, you first load the csv and get only the first csv line with the column names see https://stackoverflow.com/questions/28836781/reading-column-names-alone-in-a-csv-file with the column names you can after connecting to postgres, make a create table from the received ccolumnh nmes adnthen finalkly import the csv , you can often simply add the words you are searching in a search engine and you find an answer – nbk Apr 28 '22 at 20:49
  • Yes that I know but I am looking a way in which I can directly import the csv file as a table into postgres database. Because in that way the one you suggested I would need to specify datatype of each and everyone. When I would create a table in the postgres. – Sam Apr 28 '22 at 21:26
  • there actually a way see https://stackoverflow.com/questions/21018256/can-i-automatically-create-a-table-in-postgresql-from-a-csv-file-with-headers – nbk Apr 28 '22 at 21:30
  • The at-sign in the password must be URI-encoded, that is `%40`. Otherwise it is taken as the end of the password and the introduction of the host name – jjanes Apr 29 '22 at 00:54