CODEWITHSUNDEEP
pythonsqlite
michelle26
michelle26

Reputation: 115

Deal with new line character “\n” in Sqlite database using Python?

I have a Sqlite database named test.db, which contains two tables with structures like this:

Table1: ID INTEGER PRIMARY KEY AUTOINCREMENT, Name varchar(500), Color varchar(500)

Table2: ID INTEGER PRIMARY KEY AUTOINCREMENT, Name varchar(500), Smell varchar(500)

I am trying to join two tables using the following code

con = sqlite3.connect('test.db') 
with con: 
  cur = con.cursor()
  cur.execute("SELECT Color, Smell FROM Table1, Table2 WHERE Table1.Name = Table2.Name")

However, I failed because Table1.Name has invisible new line character “\n” inserted, while Table2.Name doesn’t, so the WHERE clause doesn’t work.

Is there a way to modify the SELECT statement a little bit so that Table1.Name = Table2.Name will work? Or do I have to regenerate Table1 to remove “\n”, but I don’t know how?

Upvotes: 0

Views: 6018

Answers (2)

Burhan Khalid
Burhan Khalid

Reputation: 174624

The first thing you should do is fix your tables so that the data is correct for your application. It looks like you have data like Apple\n in your table's name column.

Run this query to fix your table:

UPDATE Table1 SET Name = rtrim(Name,'\n')

That will remove all \n from the right side of the value of the Name column in each row, and then update that row.

Edit: @Martijn's answer will allow your query to work (assuming you don't have the same problem with Table2.Name), but you should really fix the data in your tables so that you don't have to remember to do these workarounds everytime.

Upvotes: 0

Martijn Pieters
Martijn Pieters

Reputation: 1121594

Use the replace() SQL function:

cur.execute("SELECT Color, Smell FROM Table1, Table2 "
            "WHERE replace(Table1.Name, '\n', '') = Table2.Name")

You can update your whole Table Name column to remove the newline characters altogether:

cur.execute("UPDATE Table1 SET Name = replace(Name, '\n', '') "
            "WHERE Name like '%\n%'")

Upvotes: 3

Related Questions