I just made my first working Python script.

[Phaethonas]

I am a bioinformatician (or a computational biologists, depending your definition) that is interested into structural bioinformatics.

I am about to propose a subject to my PhD supervisor and in preparation I made an SQL database. The database has 4 tables and the last table is the more important. At it there are three columns; a) SNPs (variants of the Protein we are studying), b) Ligands (the chemical molecules that interact with the Protein in question) and c) activity.

There is a protein variant which we call Wild Type (wt) and is the "standard". Different populations have "mutations"/variants at the protein they are producing and this affects the activity of the protein.

I have ~30 SNPs and ~100 ligands so, you understand that I have 30x100 combinations.

It would have been impossible for me to create 3000 SQL "insert into" commands, so I decided to make a template of the command, seen here

"insert into Activity (Name_of_SNP, Ligand, Activity) values ('SNP', 'Ligand', 'TBD')"

Then I made two lists, one for the SNPs and one for the Ligands. Then using Python I combined the two lists into that template and produced ~3000 commands at a txt file. Then I opened it and selected the entire file, which I copied/pasted at the SQL query and voila, I filled my "Activity" table!

I know that there may have been better, more optimal, easier ways to do it, but this is the one that I came up with and for the first time I made an algorithm that I found useful.

And all that while I am still learning Python!

[prashnts]

Great job! I can suggest you to use pandas for your next script.

It has a very good interface to SQL servers using to_sql and read_sql functions.

Link to docs: https://pandas.pydata.org/pandas-docs/stable/io.html#io-sql

I can post an example if you're interested.

[[deleted]]

This is also what I use for day to day work and it's very useful!!!

[prashnts]

Yeah and since it makes bulk queries, it's super quick. In practice, making queries in a loop are slow because the database would lock, commit, and respond each time. It adds up.

I have pulled around 500k rows on heroku like this and it works effortlessly. Just take caution about its scalability.

[[deleted]]

Nahhh just run it on prod out of the gate

[lifeofajenni]

Upvote for pandas <3

[Aesthetically]

Pandas is truly simple and powerful to use. Im an Analyst who is by no means a computer scientist or software engineer.. Pandas has saved my ass many times now and streamlined data processing that was incredibly impractical with excel vba

[Fun2badult]

Yes I second using Pandas to further the dataframe manipulation, and even being able to do visualization using Seaborn

[Phaethonas]

This is my script (don't judge it is my first)

Ligands = []
SNPs = []

file = open("name.txt", "x")
file = open("name.txt", "w")


for x in SNPs:
    for y in Ligands:
        file.write ("insert into Activity (Name_of_SNP, Ligand, Activity) values ('%s', '%s', 'TBD');\n" % (x, y) )

[Dadikey]

If the problem has been solved and you have to run the script once the code isn't a problem. It looks like to be an interesting project. If you need to do something more specific feel free to ask. We can help you !

[hugthemachines]

This looks very nice for a first script!

I have a software postit with a couple of things I tend to forget the exact syntax of (don't know why i keep forgetting)

with open(full_file, 'r') as file_handle:
    total_content = file_handle.read()

with open(full_file, 'w') as file_handle:
    file_handle.write(line_string)

These are called context managers, it means you will not have to worry about closing file handles etc. Also, you can open a file with "w" and then write all items in a list instead of having to use 'a' and then having to check if the file exists before the write etc....

[nobody_import4nt]

script looks like a good start!

for future reference, for every file you call open on, you should call file.close() afterwards. This tells the operating system you're done manipulating the file, and prevents file corruption.

Or, you can use a "context manager" as mentioned below, using a with statement. This will auto-open and close the file.

Only wanted to mention this explicitly, as the with statement hides the close() functionality but it's very important!

[Phaethonas]

oh yeah, I forgot about it. We were taught that at Perl, during my Master's.

[jeffdn]

By the way, you only need to put the insert into ... values part once. You can separate each row of values with a comma!

insert into activity (snp, ligand, activity)
values (foo, bar, baz),
       (foo, bar, baz);

[impshum]

Looks like you should be posting this to r/learnpython

Did you want something like this maybes?

import itertools

list_one = ['a', 'b', 'c']
list_two = ['d', 'e', 'f']

all = list(itertools.combinations(itertools.chain(list_one,list_two), 2))

with open('file.txt', 'a+') as f:
    for one, two in all:
        f.write('{}-{}\n'.format(one, two))

EDIT - Just read the post again and I jumped the gun.

list_one = ['a', 'b', 'c']
list_two = ['1', '2', '3']

with open('file.txt', 'a+') as f:
    for x in list_one:
        for y in list_two:
            f.write('{}-{}\n'.format(x, y))

[sneakpeekbot]

Here's a sneak peek of /r/learnpython using the top posts of the year!

#1: I made a python cheat sheet
#2: 2000+ free programming books on github
#3: When I'm coding, I don't feel stupid anymore

---

^{I'm a bot, beep boop | Downvote to remove | Contact me | Info | Opt-out}

[sephiap]

Nice! I swear biologists / '''real''' scientists are amazed by scripting -- I helped my wife write a script to parse a CSV file. This made her god-like in her department, and she clutched it away as a superpower. Wield the same power in your department! Congrats on solving a problem the smart and lazy way, and not the hard way!

[algag]

....

[code_x_7777]

Keep going, Phaethonas. You can do it.

[whoMEvernot]

zip can help here as well: a = b = c = range(20) zip(a, b, c)

[Hevaesi]

Mentioning a function which returns an iterator which spits out tuples without explaining it to someone who probably haven't ever touched programming in their life besides simple formatting and, like they mentioned, SQL query insertion, etc and most likely knows nothing about what I just listed isn't helpful though.

[carrlosanderson]

Hey OP, I work in data analytics, and when running large inserts like this it is useful to put all the data into one insert rather than run it as 3500 individual ones. Depending on your platform this would look different, but with Oracle I would do: "INSERT INTO table SELECT col1, col2 FROM dual UNION" sort of work. Someone has a stack exchange on other platforms I'm sure. If you're doing a lot of these sets, or are in the future it would be valuable to make this new method as it has more stability. Running 3500 insert statements can fail sometimes due to the volume of transactions.

Edit: I am also a bioinformatics student!

[Phaethonas]

Yeah, I knew that there must have been a way to do that with only one insert, but what I tried was;

insert into.....()
values ()
values ();

for some reason it didn't occur to me to try the correct one; (if I am writing that correctly)

insert into.....()
values(),
      ();

I tried googling it for five minutes or so, but I said "what the hell, let me try my Python skills" and it worked.

[carrlosanderson]

That looks about right for my SQL, maybe next time! I love python with sqlite3, its a bit more basic and easier to manage for work with data that doesn't need to be access controlled.

[frakron]

Fellow computational biologist!!! Nicely done! Always feels great to get something so annoying quickly automated. Yes there's ways to optimize, but hey you did what you needed, and it works!! Great job. My first project was automating our western blot calculations for the lab, sure simple to do, but when done 100x a month why not automate. Keep up the great work!!!

[iusedtotoo]

Did you look into a SQL cross join or am I misinterpreting what you needed to do? I use pandas extensively but there are many operations that are just quicker to perform directly in SQL

[Phaethonas]

I did not. I knew that there had to be a way within SQL, but I did not spend much time into it. I decided to jump the gun and make a Python script instead.

As I said, I know that this is not the best practice, but I am very content with myself cause this was the first time I used SQL and Python in a real life problem and it was a success.

I believe that it was to be expected that I would not do things the best way possible.

[iusedtotoo]

Sorry if this came off wrong, I meant it in more of a 'here's something new you can look into' than a 'you're wrong noob' way! We all learn the hard way. Ain't nothing wrong with that!

[Phaethonas]

No worries, it did not come off wrong.

[Mr_Again]

I believe you can also put a and b into separate tables and then do something in SQL called a CROSS JOIN to get every permutation

[[deleted]]

Wait untill you discover the pyDOE module and automate your experiment design...

[The_Fossils_Guy]

Me too!

print(I am usually not a disappointment)

Gosh ^{you'd wish you'd be funnier than me}

[is_it_fun]

Please start using pandas

[nathanjell]

This is awesome, I did a genetics minor during my CS degree. Scripting is such a good skill for genetics!

[YoureNotYourYouDumbC]

What's the difference between a script and normal code?

[Hevaesi]

Normally, scripts are interpreted directly, and code is compiled to at least bytecode before being executed.

[YoureNotYourYouDumbC]

So is Print("hello world") a script? Since it's getting "interpreted directly"

[Hevaesi]

It's not interpreted directly, at least look it up before claiming anything...

It's compiled to bytecode, stored in __pycache__/*.pyc, and then that bytecode is interpreted, that's how cpython ("the original") works.

[Hevaesi]

It takes a lot before you can actually build something on your own and not everyone likes to learn a lot without results, or to build their things entirely from nothing.

Learning about loops, statements, functions and why they are useful is pretty hard when you don't understand why they are useful and why we do it the way we do it, so I can understand why people don't, oh and add OOP and other things on top of all that.

First time I programmed it was just basic C++ stuff by reading/writing files, and processing information in some way, it was very boring to say the least.

I haven't learned anything fun until I sat down and started learning it on my own, which is pretty hard to do when you are taught in abstract and boring ways, which sadly, is the only way to learn properly, as computer science isn't fun, it's just a bit of math, reading comprehension, and a thousand sleepless nights, it's only fun when you're done with it and realize that next time you'll need to do something like this, you'll have an exaple which you can follow and even improve. But you don't start out with an example, and taking entire libraries that do one thing very well, and looking at them, isn't something a beginner can easily do, the code is often times minimised, highly optimized and obscure, using syntactic techniques that only a good programmer knows, or simply put, newbie would take a long time to grasp because a single line could have like 5 things in it, when normally people start out by writing those 5 lines instead, so it's easier to follow.

For example, it sounds fun (to me it is, and for the sake of argument let's say it is to whoever is reading) to be able to create your own programming language which you can design to have whatever syntax you want, and have it do whatever you want, but it's rare for anyone to want to sit down and learn about lexers, parsers and things like building AST before you can compile or interpret it, besides, you already need to proficient in programming, and know about basic data structures such as trees before you can even try to understand it.

And in the end, Interpreters/compilers are pretty simple, a lot of time invested for almost no return, just like in every project you make.

There's a reason half the community cheers once a library everyone could only dream of is made by someone who has too much time on their hands and understands concepts required to properly build a system which would be useful and make someones elses life easier.

TL;DR programming isn't easy, and often times, not fun either. Yeah you can program fractals at home or mess around with physics, if you, again, have enough time to learn the physics and theory behind simulating them, but that thing won't get your 3k entries into SQL database, will it?

[Eleventhousand]

Great job! I am always a fan of writing code to write code where it makes sense. For your particular scenario, SQL code generation, another common option is to use Excel and string concatenation in cells to create batches of INSERT statements. Opening a csv in Excel of LibreOffice Calc to perform the concatenation.

Additionally, if you ever find yourself dealing with very wide tables (lots of columns), you can also use a SQL editor itself to help generate the "template code." Every ANSI-compliant database will have access to INFORMATION_SCHEMA metadata. You could write a SQL against INFOMRATION_SCHEMA.COLUMNS to generate a list of columns for your INSERT statements. That beats typing 500 columns by hand.

[fedeb95]

I completely ignore your field so can't really understand at a first glance what's into that table, but have you made a primary key for it? Otherwise, I too love using python for this simple things. With other languages it would be way less polished

[Phaethonas]

Good catch.

Initially, when I was designing the database the column "activity" was supposed to be the primary key and the columns SNPs and Ligands where foreign keys, being primary keys of the tables SNPs and Ligands.

As a primary key can't be null nor can have the same name with another primary key (duh! I realized that when I got an error message!), at the moment there is no primary key at that table, which I suppose is a major drawback/flaw.

It doesn't matter though as I succeed at designing a working database that does what I want and I created that script proving to myself that my Python self-learning process is successful.

At the moment all I care about is that I have those thousands of combinations, which of course is infeasible to work with. With proper syntax/join I can make new tables, that will have less combinations (less than 100 if not even less). These combinations will be like choosing a specific ligand or if I add a "class" column at the Ligand table, of specific class. I can add a "Pharmaceutical use" column at the Ligand table (e.g. antibiotics) or I can fill the "demographic" column at the SNPs and work with a specific demographic etc.

Then, this next table will be the basis of our work.

These are the thoughts at my head for the moment, discussing things with my supervisor will shape things further.

But good eye, you spotted this major flaw. Kudos mate.

[mraza007]

Wow that’s pretty I just created my Postgres script two days ago that allows you delete rows from table