Forum

Forum breadcrumbs - You are here:ForumGeneral: Chit-chatSQL Quesstion
Please or Register to create posts and topics.

SQL Quesstion

Page 1 of 2Next

Tom

I have a sql question that I need some help with.

 

I have 3 files

filea has the key for fileb = keyb and fielc=keyc

I need information the name and address from file both files. filea has the link to each file  fileb and filec. I am sure I need to do some kind of a join to get the data but I have tried everything with no help. Can you offer me some direction?

fileb name

filec address

 

Thanks done any dx lately?

Roger

SELECT A.Stuff, B.Stuff, C.Stuff
FROM FileA AS A
JOIN FileB AS B USING(keyB)
JOIN FileC AS C USING(keyC)
;

If there are some records in A with no links to B or C and you want to show them, too, then change the JOINs to LEFT JOIN.

'fraid my DX is all on the Internet! Your callsign?

Tom thanks my call is K4AXD

That's a classic.

Did you sort the problem? I saw a post with high level code but it's gone. I was about to suggest setting a breakpoint to see what the sql statement is it passes to SQLite.

Yes I solved the problem with your help. I have been able to write several select and insert but my new problem is where I am trying to update a column.

I have this code where I am trying to update the database but keep gettin an error that says sqlite3.OperationalError: no such column: upPrefix.

I know it is not a column but is the value I want to use to update the column too. These are the values of upPrefix and fieldKey. upPrefix: 3 FieldKey: 7

If I put actual data into the sql string it works but not when I put fields that contain the same data?

I am sure some stupid operator problem but I can't figure it out. I appreciate any help you can offer.

upPrefix = enPrefix.get()

connectionOption = sqlite3.connect("gensrcwzd.wzdb")
cursorOption = connectionOption.cursor()

sql = """UPDATE citation_Fields SET cf_CitationFieldPrefix = upPrefix WHERE cf_CitationFieldKey = fieldKey"""
print(sql)
cursorOption.execute(sql)
connectionOption.commit()

Your upPrefix  and fieldKey variables are trapped inside the single sql string. You need to break them out so they can expand and concatenate them with the remaining string parts.

I'm curious: what language are you using and what is your goal?

Python. I am not very experienced in Python and am having to learn the syntex as I go. I was a programmer for about 50 years but for IBM big iron but have programmed every thing from IBM large 360 to hand held devices. I am also not experienced in SQL although I used it some a long time ago.

Well my goal long term if I live long enough is to auto magicly create a documented 4 generation pedigree chart. A data entry screen will as for a few basic bits of info then the program will do the rest. I may not live long enough but at least it keeps my mind off of the covd-19 stuff and alert so far.

Can you give me an example sql statement?

Thanks

It's the Python statement you need to modify to correctly generate the desired SQL statement. I don't know Python string operators but something like this, where & represents the string concatenator:

sql = """UPDATE citation_Fields SET cf_CitationFieldPrefix = """ & upPrefix & """ WHERE cf_CitationFieldKey = """ & fieldKey & """;"""

If citation_Fields, cf_CitationFieldPrefix, cf_CitationFieldKey are also Python variables, then they, too, must be broken out of the string so that Python can expand them.

Got it working thanks maybe not very elagent but works.

sQuote='"'

upPrefix = sQuote + enPrefix.get() + sQuote

sql = """UPDATE citation_Fields SET cf_CitationFieldPrefix = """ \
+ upPrefix \
+ """ WHERE cf_CitationFieldKey = """ \
+ str(fieldKey)

Tom

Hope I am not too much of a bother if so just let me know

I have the following code. I don't understand why the step is not being updated!

Programmer problem I suspect?? Can you offer suggestions?

step = step + 10
sql1 = "SELECT * FROM tl_link WHERE tl_name_key = 2 ORDER BY tl_entry_seq"
sql2 = "UPDATE tl_link SET tl_entry_seq = " + str(step) + " WHERE tl_name_key = 2"

cursor1 = connection.cursor()
cursor2 = connection.cursor()

for row in cursor1.execute(sql1):
step = step + 10 #<<<<<<< dose not update????? step is tl_entry_seq
print("Step" , step, sql2)
cursor2.execute(sql2)

sql = "SELECT * FROM tl_link WHERE tl_name_key = 2 ORDER BY tl_entry_seq"
print(sql)
cursor.execute(sql)
result = cursor.fetchall()
for r in result:
print("Link File entry seq should be updated: ", r)

it gives the following results:

UPDATE tl_link SET tl_entry_seq = 0 WHERE tl_name_key = 2

SELECT * FROM tl_link WHERE tl_name_key = 2 ORDER BY tl_entry_seq
Link File entry seq should be 0: (24, 2, 4, 0, 2, 'Help Link File')
Link File entry seq should be 0: (25, 2, 5, 0, 2, 'Help Link File')
Step 20 UPDATE tl_link SET tl_entry_seq = 10 WHERE tl_name_key = 2
Step 30 UPDATE tl_link SET tl_entry_seq = 10 WHERE tl_name_key = 2
SELECT * FROM tl_link WHERE tl_name_key = 2 ORDER BY tl_entry_seq
Link File entry seq should be updated: (24, 2, 4, 10, 2, 'Help Link File')
Link File entry seq should be updated: (25, 2, 5, 10, 2, 'Help Link File')

Page 1 of 2Next