Re: [sqlite] Python -> Sqlite3 import, can't retrieve values in the last column

[m.duering]

Thank you for the tip with the numbered rows, Simon (everything worked fine in 
the shell)
-- But I just discovered that my problem was caused by a \n character that my 
script failed to strip


--
Dr. Marten Düring
Digital Humanities Researcher
Centre Virtuel de la Connaissance sur l’Europe (CVCE) 
www.cvce.eu / www.cubrikproject.eu

Personal website, Historical Network Research


 On Fri, 23 Aug 2013 14:59:06 +0200 Simon Slavin 
slav...@bigfraud.org wrote  



On 23 Aug 2013, at 1:48pm, m.duering m.duer...@zoho.com wrote: 
 
 However I only get one result if I run the query 
 SELECT * FROM nodes WHERE Netzwerk="Kaufmann" 
 
SQLite uses single quotes to surround strings. Should be: 
 
SELECT * FROM nodes WHERE Netzwerk='Kaufmann' 
 
 
 This works ok and I am able to import everything into the Firefox SQlite 
Manager and Gephi. 
 
If you're not sure your data is stored properly, please download and use the 
SQLite Shell Tool to examine your database. Make sure that the results of 
SELECT commands are what you think they should be. Other tools may be useful 
but only the SQLite shell tool was written by the SQLite team and is completely 
trustworthy. 
 
http://www.sqlite.org/sqlite.html; 
http://www.sqlite.org/download.html; 
 
I also suspect you need to redesign your data structure for long-term use. 
Anything that leads to numbered columns (e.g. edges 0 to 17) is usually a sign 
that you really need to be storing things in numbered rows instead. But your 
existing code should work fine. 
 
Simon. 
___ 
sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Python -> Sqlite3 import, can't retrieve values in the last column

[Simon Slavin]

On 23 Aug 2013, at 1:48pm, m.duering  wrote:

> However I only get one result if I run the query
> SELECT * FROM nodes WHERE Netzwerk="Kaufmann"

SQLite uses single quotes to surround strings.  Should be:

SELECT * FROM nodes WHERE Netzwerk='Kaufmann'


> This works ok and I am able to import everything into the Firefox SQlite 
> Manager and Gephi.

If you're not sure your data is stored properly, please download and use the 
SQLite Shell Tool to examine your database.  Make sure that the results of 
SELECT commands are what you think they should be.  Other tools may be useful 
but only the SQLite shell tool was written by the SQLite team and is completely 
trustworthy.




I also suspect you need to redesign your data structure for long-term use.  
Anything that leads to numbered columns (e.g. edges 0 to 17) is usually a sign 
that you really need to be storing things in numbered rows instead.  But your 
existing code should work fine.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Python -> Sqlite3 import, can't retrieve values in the last column

[m.duering]

Hi all,

this is my first project using Sqlite3 so I might have done something silly 
along the way. 
I can not run queries for the last columns of my tables. I either get only the 
very last row or nothing where there should be hundreds of entries which match 
the query. 

Here is what I did:

I read network data from a csv file into Sqlite using the following code in 
Python and transformed them into unicode.
Where edges and nodes are represented as a list which contains a list of the 
node and its attributes.
One sample node entry: 
[(u'1480', u'Z_Avi', u'2', u'3', u'1', u'4', u'99', u'3', u'99', u'11', 
u'Kaufmann')]


to enter edges:


conn = sqlite3.connect("test.db")
c = conn.cursor()

for i in range(len(edges)):

edge1 = [(
edges[i][0],
edges[i][1],
edges[i][2],
edges[i][3],
edges[i][4],
edges[i][5],
edges[i][6],
edges[i][7],
edges[i][8],
edges[i][9],
edges[i][10],
edges[i][11],
edges[i][12],
edges[i][13],
edges[i][14],
edges[i][15],
edges[i][16],
edges[i][17],
)]

i = 0

c.executemany("INSERT INTO edges 
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", edge1)
conn.commit()
print "inserted records: edges"

conn.close()




and then to enter nodes:




conn = sqlite3.connect("test.db")
c = conn.cursor()
for i in range(len(nodes)):

node1 = [(
nodes[i][0],
nodes[i][1],
nodes[i][2],
nodes[i][3],
nodes[i][4],
nodes[i][5],
nodes[i][6],
nodes[i][7],
nodes[i][8],
nodes[i][9],
nodes[i][10],
)]
i = 0

c.executemany("INSERT INTO nodes VALUES(?,?,?,?,?,?,?,?,?,?,?)", node1)
conn.commit()

conn.close()


This works ok and I am able to import everything into the Firefox SQlite 
Manager and Gephi. However I only get one result if I run the query
SELECT * FROM nodes WHERE Netzwerk="Kaufmann"

Any thoughts and tips on this are much appreciated.

Best, Marten





--
Dr. Marten Düring
Digital Humanities Researcher
Centre Virtuel de la Connaissance sur l’Europe (CVCE) 
www.cvce.eu / www.cubrikproject.eu

Personal website, Historical Network Research

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users